Import Excel to MS Access Problem

[prakashdotc]

Hi All,

I'm having a Excel Sheet With first column which has numeric data for first 70 or 80 records then comes some string data, here i'm using this statement to import data from excel to MS Access DataBase, but its not retriving the records which were in string data type, is there any other way to do this...?

SELECT [Item #], [Description], [Pack Size], [Price] FROM
[Database=C:\Test.xls;Excel 8.0;HDR=Yes;IMEX=2].[Sheet1$] Where
[Item #] Is Not Null

thank's in Advance.

- Prakash.C

 

[Guus2005]

You can import the spreadsheet by setting an import definition. Import the spreadsheet by hand and save the specification. in this spec you must define the first column as a textfield.
When you import the spreadsheet using code you can use the docmd.TransferSpreadsheet command.

 

[prakashdotc]

Thank U Guus,

it seems docmd.TransferSpreadsheet is for VBA but i need some solution to be done in vb.net or c#, is there any other way to do that,

thanks

- Prakash.C

 

[Guus2005]

That is a completely different question. Can't help you there.

 

[anthonys]

When access imports data it sets the column type using the first rows data. In the past i have entered a dummy row of data with text in column A Row 1 and then subsequently deleted that record as part of the import.

Alternatively you could try ordering your spreadsheet, prior to import, so that column A row 1 contains a text value.

It's a fudge but it will at least get your data in.