Import .csv question

[Nathan]

I have a text file that contains 10 items on each line all seperated by a
comma. The problem that I am having is that I don't want to have 10 fields
for each record. I would like to be able to import the data so that each
comma would be a new record. Is this possible with out manually going into
the file and putting a carraige return in place of each comma?

 

[John Nurick]

Hi Nathan,

It sounds as if you have something like this

44,546,333,445,21,9,66,65,43,33
34,1,23,34,56...

and want to get
44
546
333
445
21
9
66
...

If so, the simplest way is usually to open the file in a text editor or
in Microsoft Word and then do a single search and replace operation. In
Word you'd replace
,
with
^p
and then save the result as a text file.

If you have Perl, sed or awk on your system you can do the same
operation from the command line, e.g.
perl -ibak -pe "s/,/\n/g" "D:\Folder\My Text File.txt"

Finally, if this is going to be a production task that needs to be
handled automatically and invisibly, it can all be done in Access VBA:
post back if that's the case.

 

[Nathan]

John,

Thanks for your suggestions. What you mentioned is exactly what I would
like to do. Yes, I would like to have this done through the source code. I
have hundreds of files that I would like to import into my database in this
manner. I have some experience with VBA, but not much. I am still a
beginner/intermediate with access, so any coding examples would be great.

 

[Nathan]

I forgot to mention, I don't have perl, sed, or awk on my machine.

 

[Gregory Paret]

John,

Thanks for your suggestions. What you mentioned is exactly what I would
like to do. Yes, I would like to have this done through the source code. I
have hundreds of files that I would like to import into my database in this
manner. I have some experience with VBA, but not much. I am still a
beginner/intermediate with access, so any coding examples would be great.

You could use something like

Sub importNumberList(strFileName As String, strTableName As String,
strFieldName As String)
Dim fn, n
Dim db As Database, rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM " & strTableName)

fn = FreeFile
Open strFileName For Input As #fn

On Error GoTo inl_done
Do Until EOF(fn)
Input #fn, n
If Not IsEmpty(n) Then
rs.AddNew
rs(strFieldName) = n
rs.Update
End If
Loop
inl_done:
Close #fn
End Sub

to read each number, in sequence, from the input file and write it to a table.
If you want to process multiple files, look into the Dir() function to loop
through the files.

-Greg.

 

[John Nurick]

There are several ways of doing it. One is to build and execute the SQL
statement for a query that unions each of the 10 fields in the text file
into a single column and appends it to a table in your database. Here's
what the query looks like in my test system, where the table is called
Nathan, its single field is called XXX, and we're importing from the
file C:\Temp\Nathan\n1.csv:

INSERT INTO Nathan (XXX) SELECT XXX FROM
(
SELECT F1 AS XXX FROM [Text;HDR=No;Database=C:\TEMP\Nathan\;].N1#csv
UNION
SELECT F2 AS XXX FROM [Text;HDR=No;Database=C:\TEMP\Nathan;].N1#csv
UNION
SELECT F3 AS XXX FROM [Text;HDR=No;Database=C:\TEMP\Nathan;].N1#csv
UNION
SELECT F4 AS XXX FROM [Text;HDR=No;Database=C:\TEMP\Nathan;].N1#csv
UNION
SELECT F5 AS XXX FROM [Text;HDR=No;Database=C:\TEMP\Nathan;].N1#csv
UNION
SELECT F6 AS XXX FROM [Text;HDR=No;Database=C:\TEMP\Nathan;].N1#csv
UNION
SELECT F7 AS XXX FROM [Text;HDR=No;Database=C:\TEMP\Nathan;].N1#csv
UNION
SELECT F8 AS XXX FROM [Text;HDR=No;Database=C:\TEMP\Nathan;].N1#csv
UNION
SELECT F9 AS XXX FROM [Text;HDR=No;Database=C:\TEMP\Nathan;].N1#csv
UNION
SELECT F10 AS XXX FROM [Text;HDR=No;Database=C:\TEMP\Nathan;].N1#csv
);


The code to import data from a single file would be something like this
(untested air code). I'll leave it to you to split the FileSpec argument
(e.g. C:\Temp\Nathan\N1.csv) into strFolder, strFileName and strFileExt,
using VBA functions such as InStr(), Left() and Mid(), and of course
you'll need to substitute your actual table and field names.

Sub ImportOneFile(FileSpec As String)
Dim strSQL As String
Dim strFolder As String
Dim strFileName As String
Dim strFileExt As String
Dim strSub As String
Dim strFROM As String
Dim j as Long

'start of SQL statement
strSQL = "INSERT INTO Nathan (XXX) SELECT XXX FROM (" & vbCrLf

'*** Replace this with code to split up FileSpec ***
strFolder = "C:\Temp\Nathan\"
strFileName = "N1"
strFileExt = "csv"

'Assemble parts of file spec into FROM clause ready for use
strFROM = " AS XXX FROM [Text;HDR=No;Database=" _
& strFolder & ";]." & strFileName & "#" & strFileExt _
& vbCrLf

For j = 1 to 10
If j = 0 Then
strSub = ""
Else
strSub = "UNION" & vbCrLf
End If
strSub = strSub & " SELECT F" & CStr(j) & strFROM
strSQL = strSQL & strSub
Next j

strSQL = strSQL & ");"

CurrentDb.Execute strSQL, dbFailOnError

End Sub

 

[Nathan]

John,

Thanks for your help with this. I found that the SQL statement that you
gave me was almost what I wanted. I changed a few things and it work great.
If anyone is interested in seeing the code, I will post it.

Nathan

There are several ways of doing it. One is to build and execute the SQL
statement for a query that unions each of the 10 fields in the text file
into a single column and appends it to a table in your database. Here's
what the query looks like in my test system, where the table is called
Nathan, its single field is called XXX, and we're importing from the
file C:\Temp\Nathan\n1.csv:

INSERT INTO Nathan (XXX) SELECT XXX FROM
(
SELECT F1 AS XXX FROM [Text;HDR=No;Database=C:\TEMP\Nathan\;].N1#csv
UNION
SELECT F2 AS XXX FROM [Text;HDR=No;Database=C:\TEMP\Nathan;].N1#csv
UNION
SELECT F3 AS XXX FROM [Text;HDR=No;Database=C:\TEMP\Nathan;].N1#csv
UNION
SELECT F4 AS XXX FROM [Text;HDR=No;Database=C:\TEMP\Nathan;].N1#csv
UNION
SELECT F5 AS XXX FROM [Text;HDR=No;Database=C:\TEMP\Nathan;].N1#csv
UNION
SELECT F6 AS XXX FROM [Text;HDR=No;Database=C:\TEMP\Nathan;].N1#csv
UNION
SELECT F7 AS XXX FROM [Text;HDR=No;Database=C:\TEMP\Nathan;].N1#csv
UNION
SELECT F8 AS XXX FROM [Text;HDR=No;Database=C:\TEMP\Nathan;].N1#csv
UNION
SELECT F9 AS XXX FROM [Text;HDR=No;Database=C:\TEMP\Nathan;].N1#csv
UNION
SELECT F10 AS XXX FROM [Text;HDR=No;Database=C:\TEMP\Nathan;].N1#csv
);


The code to import data from a single file would be something like this
(untested air code). I'll leave it to you to split the FileSpec argument
(e.g. C:\Temp\Nathan\N1.csv) into strFolder, strFileName and strFileExt,
using VBA functions such as InStr(), Left() and Mid(), and of course
you'll need to substitute your actual table and field names.

Sub ImportOneFile(FileSpec As String)
Dim strSQL As String
Dim strFolder As String
Dim strFileName As String
Dim strFileExt As String
Dim strSub As String
Dim strFROM As String
Dim j as Long

'start of SQL statement
strSQL = "INSERT INTO Nathan (XXX) SELECT XXX FROM (" & vbCrLf

'*** Replace this with code to split up FileSpec ***
strFolder = "C:\Temp\Nathan\"
strFileName = "N1"
strFileExt = "csv"

'Assemble parts of file spec into FROM clause ready for use
strFROM = " AS XXX FROM [Text;HDR=No;Database=" _
& strFolder & ";]." & strFileName & "#" & strFileExt _
& vbCrLf

For j = 1 to 10
If j = 0 Then
strSub = ""
Else
strSub = "UNION" & vbCrLf
End If
strSub = strSub & " SELECT F" & CStr(j) & strFROM
strSQL = strSQL & strSub
Next j

strSQL = strSQL & ");"

CurrentDb.Execute strSQL, dbFailOnError

End Sub

I forgot to mention, I don't have perl, sed, or awk on my machine.