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

Create Microsoft Excel documents with the Open Xml SDK with charts

See: http://blogs.msdn.com/brian_jones/archive/2008/11/04/document-assembly-solution-for-spreadsheetml.aspx
You can now create Microsoft Office Excel documents which are compatible with the desktop client, with use of the Open Xml SDK. The created document can contain charts, formula’s etc. The data is inserted into Microsoft Excel by using LINQ.

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.