Re: Import .csv question
From: Nathan (Nathan_at_discussions.microsoft.com)
Date: 03/26/05
- Next message: G. Vaught: "Re: Linking to exteranal table 2 questions."
- Previous message: Paul: "Linking to exteranal table 2 questions."
- In reply to: John Nurick: "Re: Import .csv question"
- Next in thread: Gregory Paret: "Re: Import .csv question"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 26 Mar 2005 14:59:02 -0800
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
"John Nurick" wrote:
> 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
>
>
>
>
>
> On Fri, 25 Mar 2005 07:03:01 -0800, Nathan
> <Nathan@discussions.microsoft.com> wrote:
>
> >I forgot to mention, I don't have perl, sed, or awk on my machine.
> >
> >"Nathan" wrote:
> >
> >> 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.
> >>
> >> "John Nurick" wrote:
> >>
> >> > 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.
> >> >
> >> > On Thu, 24 Mar 2005 16:39:07 -0800, Nathan
> >> > <Nathan@discussions.microsoft.com> wrote:
> >> >
> >> > >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 [Microsoft Access MVP]
> >> >
> >> > Please respond in the newgroup and not by email.
> >> >
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>
- Next message: G. Vaught: "Re: Linking to exteranal table 2 questions."
- Previous message: Paul: "Linking to exteranal table 2 questions."
- In reply to: John Nurick: "Re: Import .csv question"
- Next in thread: Gregory Paret: "Re: Import .csv question"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
