Access Data Project Importing CSV File In VBA?

There is an easier way to import a CSV! You can use the Microsoft Text Odbc Driver Sub Import() Dim conn as new ADODB. Connection Dim rs as new ADODB.

Recordset Dim f as ADODB. Field conn. Open "DRIVER={Microsoft Text Driver (*.

Txt; *. Csv)};DBQ=c:\temp;" rs. Open "SELECT * FROM test.

Txt", conn, adOpenStatic, adLockReadOnly, adCmdText While Not rs. EOF For Each f In rs. Fields Debug.

Print f. Name & "=" & f. Value Next Wend End Sub You change from a Select to an Insert into combined with a select and there you are There are some settings you can do in the registry, in the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text : Format: TabDelimited, CSVDelimited, Delimited(X) where X=some char FirstRowHasNames: 0,1 CharacterSet: OEM, ANSI.

There is an easier way to import a CSV! You can use the Microsoft Text Odbc Driver. Sub Import() Dim conn as new ADODB.

Connection Dim rs as new ADODB. Recordset Dim f as ADODB. Field conn.

Open "DRIVER={Microsoft Text Driver (*. Txt; *. Csv)};DBQ=c:\temp;" rs.

Open "SELECT * FROM test. Txt", conn, adOpenStatic, adLockReadOnly, adCmdText While Not rs. EOF For Each f In rs.

Fields Debug. Print f. Name & "=" & f.

Value Next Wend End Sub You change from a Select to an Insert into combined with a select and there you are. There are some settings you can do in the registry, in the key \\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text: Format: TabDelimited, CSVDelimited, Delimited(X) where X=some char FirstRowHasNames: 0,1 CharacterSet: OEM, ANSI.

Is there some reason you consider this approach superior to just using DoCmd. TransferText? It's certainly going to be less efficient, don't you think?

You also haven't actually imported anything, just printed out the data in the immediate window. – David-W-Fenton Aug 5 '10 at 19:45 If you read my post again, you will find the sentence where I write about changing the Select to Insert into. And TransferText is fine, as long the structure is exactly the same.

Doing it this way, it can do some transformation before writing to the db. – dwo Aug 6 '10 at 7:42.

In many cases, the easiest way to import CSV is with the TransferText method. Refer to this MSDN link for details: TransferText Method Access 2003 VBA Language Reference Here is an example TransferText command to import C:\SomeFolder\DataFile. Csv into a table named tblImport.

The last parameter, HasFieldNames, is False to indicate the CSV file doesn't include field names. DoCmd. TransferText acImportDelim, "YourCustomSpecificationName", _ "tblImport", "C:\SomeFolder\DataFile.

Csv", False The SpecificationName parameter is optional. However, you can often get better results by creating your own import specification and including its name in the TransferText command. The specification allows you to identify which table fields to load the data into, adjust data types, and a whole host of other options.

You can create your own import specification when you manually import your data file ... select your custom import options, and save those choices as a named specification. (Look for an "Advanced" button on the Import Wizard dialog. ).

BULK INSERT is generally faster, and it will work on an X64 machine. The text driver mentioned above will probably not work in some X64 environments. I would reccomend NOT using the format file, it's a lot simpler without it.

msdn.microsoft.com/en-us/library/ms18836... BULK INSERT AdventureWorks2008R2.Sales. SalesOrderDetail FROM 'f:\orders\lineitem. Tbl' WITH ( FIELDTERMINATOR =' |', ROWTERMINATOR =' |\n' ).

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.