Newbie-How do I import a PIPE delimited file into Access tables?

[Guest]

I need to automatically import a pipe delimited file in Access and create 2
tables from the data. And I don't know where to begin. Here are the details
as I can understand it.

The file comes to me with 2 rows per record. And has about 1000+ records.

The first row is considered the order header, which consists of vendor name
and number, order number, bill to, ship to etc. This row has about 82 fields.

The second row is considered the order detail which consists of a matching
order number, item #, item description, price, handling, ship method, etc...
This row has about 60 fields.

Please help...

 

[tina]

well, using strictly the Import Wizard (rather than using code to parse the
text file, or other code-based solutions), here's one approach:

dump all the data into a "temp" table, with all fields as text.
assuming you want all the "order header" rows in one table, and all the
"order details" rows in the other table, create a destination table to hold
each set of data - make sure you set the field names and data types
appropriately for each field.
since the header rows have substantially more fields than the details rows,
identify a field in the temp table that is *never* Null in a header row AND
*always* Null in an order detail row. an obvious choice is one of those
"extra" twenty-odd fields that hold header row data only.
create an Append query to dump the temp table data into the header
destination table. on the field you identified in the previous step, set a
criteria as Is Not Null. that should effectively filter out the order
details rows.
create another Append query to dump the temp table data into the order
details destination table. include that same field you identified earlier
(though you don't need to include any of the other twenty-odd fields that
don't apply to the details rows), and set a criteria as Is Null. that should
effectively filter out the header rows.

if you'll be doing this on a repetitive basis, create an Import
Specification for the initial data import to the temp table. save the Append
queries you build. once you've tested the process and have it working
correctly, you can automate the import/append steps to run at the click of a
button, from a macro or VBA. don't forget to include a Delete query to
remove all data from the temp table, *after* it has been appended to the
destination tables.

hth

 

[John Nurick]

Hi,

One way to do this is to split the file into two, one containing only
order headers and the other only order details. Each of these can then
be imported in the usual way.

Here's a simple VBA procedure that will do the splitting if you pass it
the names and locations for the existing file and the two you want to
create. It compiles but hasn't been tested, so you may need to fiddle
with it.

Public Sub SeparateSourceLines( _
SourcePath As String, _
DestOrders As String, _
DestDetails As String)

Const N_FIELDS As Long = 75 'lines with more than
'this number of fields are assumed to be orders,
'those with fewer are details.

Dim lngSource As Long
Dim lngOrders As Long
Dim lngDetails As Long
Dim strLine As String

'open files
lngSource = FreeFile()
Open SourcePath For Input As #lngSource
lngOrders = FreeFile()
Open DestOrders For Output As #lngOrders
lngDetails = FreeFile()
Open DestDetails For Output As #lngDetails

Do Until EOF(lngSource)
Line Input #lngSource, strLine
If Len(strLine) - Len(Replace(strLine, "|", "")) > N_FIELDS Then
'This is an order record
Print #lngOrders, strLine
Else 'detail record
Print #lngDetails, strLine
End If
Loop

'Close files
Close #lngSource
Close #lngOrders
Close #lngDetails

End Sub

After using this (or something like it) to separate the two kinds of
records, import the two "simple" files into their respective tables.
Because they're pipe delimited, you'll need to create an import
specification for each, and include the name of the specification when
you call DoCmd.TransferText to import them.

I hope this is clear enough.

 

[Guest]

I'm very new with scripting, so bear with me. I'm having trouble running the
script, I keep getting a compliation error(2,18) expected ')' What does that
mean???

Here's your code that I modified: Notice all I did was to change the 75 to
80 and insert the filenames of my source and destination files?

Public Sub SeparateSourceLines( _
SourcePath As String, _
DestOrders As String, _
DestDetails As String)

Const N_FIELDS As Long = 80 'lines with more than
'this number of fields are assumed to be orders,
'those with fewer are details.

Dim lngSource As Long
Dim lngOrders As Long
Dim lngDetails As Long
Dim strLine As String

'open files
lngSource = FreeFile()
Open "neworders.txt" For Input As #lngSource
lngOrders = FreeFile()
Open "orders.txt" For Output As #lngOrders
lngDetails = FreeFile()
Open "details.txt" For Output As #lngDetails

Do Until EOF(lngSource)
Line Input #lngSource, strLine
If Len(strLine) - Len(Replace(strLine, "|", "")) > N_FIELDS Then
'This is an order record
Print #lngOrders, strLine
Else 'detail record
Print #lngDetails, strLine
End If
Loop

'Close files
Close #lngSource
Close #lngOrders
Close #lngDetails

End Sub

 

[John Nurick]

The only way I can think you'd get this error is if you've saved the
procedure in a text file and are trying to execute it as a VBScript.

I wouldn't do it that way. Instead,

-create a new module in your Access database

-If necessary, add
Option Explicit
at the beginning.

-Paste the VBA procedure into the module

-Save the module (but don't call it SeparateSourceLines).

You can then execute it with a statement like this (e.g. in the
Immediate pane for testing, or in a VBA procedure that collects the
source file name from the user, separates it into two files, and then
calls DoCmd.TransferText to import each of them):

SeparateSourceLines "C:\Folder\Subfolder\Data.txt",
"C:\Folder\subfolder\Orders.txt", "C:\Folder\subfolder\details.txt"