Generate SQL insert statements from Excel / Csv file online

Lets say you get a Microsoft Excel file containing the following information:

image

 

If you want to import these record in a SQL table, just save the excel file as CSV.

Then upload it to http://www.convertcsvtomysql.com/

 

 

image

 

This will generate the following SQL script:

CREATE TABLE IF NOT EXISTS ‘product’
(
‘name’ VARCHAR(255) NOT NULL,
‘description’ VARCHAR(255) NOT NULL
)

INSERT INTO ‘product’ VALUES
(‘Car’, ‘This is the car description’),
(‘Bike’, ‘This is the bike description’),
(‘Bus’, ‘This is the bus description’);

 

Remove the "create table" peace if your table already exists.

Add a WebService reference to a Microsoft Office Excel sheet

> Start you’re webservice
> Start Microsoft Office Excel
> Show the Control Toolbox toolbar: > View > Toolbars > Control Toolbox
> Enter Design mode by clicking the “Design Mode” button on the Control Toolbox
> Add a button to you’re Excel sheet
> Double click the button, this will op en de Microsoft Visual Basic Editor
> In the Microsoft Visual Basic Editor click: Tools > Web Service Reference… >
> On the Microsoft Office 2003 Web Services Toolkit dialog click: “Web Service URL” and enter you’re Web Service URL, click Search
> In the Search Results area, check you’re Web Service and click Add

image

This will generate a class “clsws_Service” with methods foreach WebMethod of the WebService, but the constant “Private c_WSDL_URL As String” should be changed to “Public URL As String”, so you can set the URL dynamic like:

    Dim service As New clsws_Service
    Dim data As String
    service.URL = ActiveSheet.Cells(1, "A").Value
    data = service.wsm_GetOpsMail(ActiveSheet.Cells(2, "A").Value, ActiveSheet.Cells(3, "A").Value)

More information can be found on: http://www.brainbell.com/tutorials/ms-office/excel/Access_SOAP_Web_Services_From_Excel.htm

Microsoft Excel – VBA – Compile error: Can't find project or library SoapClient30

If you have a VBA macro in Microsoft Excel and a webreference to a webservice, you might get the famous Compile error: Can’t find project or library SoapClient30.
In mine case it wasn’t missing the C:\Program Files (x86)\Common Files\microsoft shared\OFFICE12\MSSOAP30.DLL but an old reference to the Office 11 components library.

Solution
> Open the Microsoft visual basic editor
> Tools > References…
> Remove all dll’s with "MISSING" for it, if you don’t have any start with deleting de selected dll until a dll appears witch is missing.

Remove ExpandedColunCount and ExpandedRowCount when using Microsoft Exel (*.xml) file as template

When you use a Microsoft Excel "*.xml" file as template for generating Microsoft Excel reports, remove the ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1" x:FullRows="1"

from the <Table> tag under the <Worksheet> tag. If you don’t remove these counters a error will occur, because the row and or column count is higher then 1:

Problem During Load
Problems came up in the following areas during load:
Worksheet Setting
This file cannot be opened because of errors. Erros are listed in C:\Users……\Content.MSO\7BF935F6.log

 

<Worksheet ss:Name="Sheet3">
  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1" x:FullRows="1">
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
</Worksheet>

 

An other error that can occur is:

XML ERROR in Table
REASON:    Bad Value
FILE:    C:\Temp\MyTest.xml
GROUP:    Table
TAG:    Row
ATTRIB:    Index
VALUE:    128

 

This was caused by the line:

<ss:Row ss:Index="128" ss:AutoFitHeight="0" ss:Height="12.9375">

Cause and solution

The index was not correct, by removing the ss:Index="128" ss:AutoFitHeight="0" ss:Height="12.9375" from the Row tag, the file could be opened correctly again.