Import Spec CSV

[benatom]

I only need certain columns from a csv. I built an import spec which
is referred to like this:

DoCmd.TransferText acImportDelim, "CSV_Import_Spec", "tbl_TEMP",
strCSV_path, True, ""

If the csv was formatted exactly the same for each import, the import
spec would work fine every time.

However, occasionally new columns which I do not need are added to the
csv (it will always have the columns I need), thus the import using
the predefined import spec "CSV_Import_Spec" fails.

Is there an alternative method for importing the columns I need from a
csv, possibly not using a pre-built import spec?

 

[Douglas J. Steele]

There's no requirement to have an import spec. See what happens when you
leave it out:

DoCmd.TransferText acImportDelim, , "tbl_TEMP", strCSV_path, True, ""

 

[benatom]

If I leave it out, it then errors when it is trying to put data into
columns that don't exist in the table (tbl_TEMP).

If I alter the line:

DoCmd.TransferText acImportDelim, , "tbl_NewTable", strCSV_path, True,
""

A new table is created with all columns, but the data is not formatted
correctly, i.e. only the numbers in a column that has both numbers and
text import. The text values are null. The precreated import spec,
CSV_Import_Spec, had a text delimiter (").

 

[Douglas J. Steele]

If I leave it out, it then errors when it is trying to put data into
columns that don't exist in the table (tbl_TEMP).

If I alter the line:

DoCmd.TransferText acImportDelim, , "tbl_NewTable", strCSV_path, True,
""

A new table is created with all columns, but the data is not formatted
correctly, i.e. only the numbers in a column that has both numbers and
text import. The text values are null. The precreated import spec,
CSV_Import_Spec, had a text delimiter (").

I thought there was a registry setting that would let you default the
specification to using ", but unfortunately I can't find it right now.

Hopefully someone else will have a suggestion.

Sorry.

 

[Larry Linson]

I only need certain columns from a csv. I built
an import spec which is referred to like this:

DoCmd.TransferText acImportDelim, "CSV_Import_Spec",
"tbl_TEMP", strCSV_path, True, ""

If the csv was formatted exactly the same for each
import, the import spec would work fine every time.

However, occasionally new columns which I do not
need are added to the csv (it will always have the
columns I need), thus the import using the
predefined import spec "CSV_Import_Spec" fails.

Is there an alternative method for importing the
columns I need from a csv, possibly not using a
pre-built import spec?

Yes, you can write VBA code to read an external file.You will have to have a
way to determine the columns you want... then read the sequential file
record by record, or even field by field, save the pertinent text into
fields in a record and write that record to a table. Open, Input #, and
Close are pertinent commands for reading external text files; I recommend
you use Data Access Objects (DAO) to write the records.

I do some client processing each month with an input that is a fixed format
text file... it was convenient for the original author of the software to
use an Import Spec, and it is/would be definitely inconvenient to try to use
code to locate where fields start and stop. I check the input data format,
manually, every month, and sometimes have to create a new format by manually
"reading" the new input and saving the Import Spec. It is not one of the
simplest, most straightforward features in Access, but I am testimony to the
fact that it can be accomplished. In other situations, with slightly
different type and quality of data, I have used VBA/DAO code to "import"
delimited and fixed text files.

Larry Linson
Microsoft Office Access MVP

 

[benatom]

Thanks for your advice. I did a search and found some code that would
do something similar, but using ADO to pull data into a spreadsheet.
In this house we generally use DAO only. On that account alone, I'd
rather be doing this in DAO (ideas?). I created this code to pull
only those columns I need from the CSV into Access. I submit this as
a possible solution, but please realize that I my ADO could possibly
need some expert tweaking. It does work.

Sub Import_CSVx(strCSVname As String)

Dim SourceSQL As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CurrentProject.AccessConnection

On Error GoTo ADO_ERROR

Dim strSQL As String

cn.Close

cn = New ADODB.Connection

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:
\DATA\;Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"";Persist
Security Info=False"

cn.ConnectionTimeout = 40

cn.Open

Set rs = New ADODB.Recordset

rs.ActiveConnection = cn

SourceSQL = "Select Brand, BusGrp, Cat, FamDesc, Dept, UPCDesc,
UPC_ID, UPC_NUM From " & strCSVname & ""

rs.Source = SourceSQL
rs.Open

rs.MoveFirst

Do Until rs.EOF

strSQL = " VALUES (" & _
"'" & Replace(Nz(rs!Brand), "'", "''") & "'," & _
"'" & Replace(Nz(rs!BusGrp), "'", "''") & "'," & _
"'" & Replace(Nz(rs!Cat), "'", "''") & "'," & _
"'" & Replace(Nz(rs!FamDesc), "'", "''") & "'," & _
"'" & Replace(Nz(rs!Dept), "'", "''") & "'," & _
"'" & Replace(Nz(rs!UPCDesc), "'", "''") & "'," & _
"'" & rs!UPC_ID & "'," & _
"'" & rs!UPC_NUM & "')"

CurrentDb.Execute "INSERT INTO tMaster " & strSQL

rs.MoveNext

Loop

rs.Close
If Not rs Is Nothing Then Set rs = Nothing
If Not cn Is Nothing Then Set cn = Nothing

ADO_ERROR:

If Err <> 0 Then

Debug.Assert (Err = 0)

MsgBox (Err.Description)

Resume Next

End If

End Sub

 

[benatom]

One more note. You can set MaxScanRows = 0 (scan all rows to
determine data type) by using a schema.ini file like below.

Columns can be created within the schema.ini, similar to what an
Access import specification does.

I have found that as with an Access Import Specification the columns
in the source file must match the columns defined in the schema.ini
file.

The schema.ini must be placed in the same folder as the source csv

On schema.ini:

http://msdn.microsoft.com/en-us/library/ms709353.aspx

The schema.ini could be created through code, here is one way:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;155512

[DataFileName.csv]
ColNameHeader = True
Format = CSVDelimited
MaxScanRows = 0

 

[Larry Linson]

By choice, I do not work with ADO except on paying work where it already
exists in the client's database. Perhaps someone who uses ADO will offer
some assistance.

Larry Linson
Microsoft Office Access MVP