Importing Table from Text File - Complicated

[djruni]

Hello - I want to import a text file into a table in a database. The text file looks like this:

00001 06006025 1420 0010 Health Insurance 7
00001 06006025 1425 0010 Life Insurance 7
00001 06006025 1430 0010 Disability 7
00001 06006025 1440 0010 Profit Sharing 7

There is no consistent delimiter. The text field is making things complicated because there is no accurate way to separate the text field from the last number in each line ('7') which needs to be stored in a different column.

Is there a way to insert quotations arond the text string so the quotation mark can be used to distinguish it when importing into excel or a database table. This may work because the starting position of the text string is constant. The ending position would have to be defined as the place where the number appears ('7' in this case). Then, quotations need to be placed around the text string.

Is this possible? Any advice will help tons!!!

 

[GohDiamond]

This is one option, just down and dirty:
Import the text into Excel using fixed delimiters as much as you can. To separate out the '7' or any other number at the end of the Text cell use the next adjacent column(F) with a formula like =right(E1,1); drag the formula to the bottom of the table. Then select the whole column(F) and Paste Special <Values> to preserve the number permanently in Column F. To get rid of the number from Column E use Find and replace a couple of times for those numbers which show up in the column E text field.

You can import the normalized excel table into Access without worry.

You may also use Text to Columns wizard in combination with this idea if appropriate. Data\Text to Columns; but make sure that are blank columns to the right before you do so; Undo will be your friend if you haven't allocated enought columns to the right

Cheers!
Goh

 

[djruni]

Thanks for the suggestion. I forgot to mention there are 81,000+ records!

The number '7' may appear anywhere from column C to Column G...

 

[WayneRyan]

dj,

A little VBA code can do this:

Dim strTemp As String
Dim rst As DAO.Recordset

Set rst = CurrentDb.Openrecordset("Select * From YourTable")

Open "C:\YourTextFile.txt" For Input As #1
Line Input #1, strTemp

While Not rst.EOF And Not rst.BOF
   rst.AddNew
   rst!Field1 = Mid(strTemp,  1, 5)
   rst!Field2 = Mid(strTemp,  7, 8)
   rst!Field3 = Mid(strTemp, 16, 4)
   rst!Field4 = Mid(strTemp, 21, 4)
   rst!Field5 = Mid(strTemp, 26)
   rst!Field6 = Right(Field5, 1)
   rst!Field5 = Left(Field5, Len(Field5) - 2)
   rst.Update
   Line Input #1, strTemp
   Wend
Close #1
rst.Close
Set rst = Nothing

hth,
Wayne

 

[djruni]

Wayne,

Thanks. Can you guide me in how and where to input the code? Do I need to have the file in Access first and right the code there somewhere?

If you can explain some of the primary logic in ur code lines Ill follow it as its intended. Thanks!...

 

[WayneRyan]

dj,

The code can be put on a Command Button.
The file is not "in Access", it is just the disk file --> C:\YourTextFile.txt

I had a couple of typos, here's the annotated code:

Dim strTemp As String      <-- String to hold each line of the file
Dim rst As DAO.Recordset   <-- Your "connection" to your table

Set rst = CurrentDb.Openrecordset("Select * From YourTable")  <- Open connection to your table

Open "C:\YourTextFile.txt" For Input As #1   <-- Open the disk file and read
Line Input #1, strTemp                       <-- the first line in

While Not EOF(1)    <-- Loop while there are still lines in the file
   rst.AddNew       <-- For each line of the disk file, add a record
   rst!Field1 = Mid(strTemp,  1, 5)   <-- Put segments of the line into their respective fields
   rst!Field2 = Mid(strTemp,  7, 8)
   rst!Field3 = Mid(strTemp, 16, 4)
   rst!Field4 = Mid(strTemp, 21, 4)
   rst!Field5 = Mid(strTemp, 26)      <-- Field 5 has a " 7" at the end
   rst!Field6 = Right(rst!Field5, 1)  <-- Put the "7" into Field 6
   rst!Field5 = Left(rst!Field5, Len(rst!Field5) - 2)   <-- Remove the " 7" from Field5
   rst.Update               <-- Save the new Access record
   Line Input #1, strTemp   <-- Read next line of file
   Wend
Close #1
rst.Close
Set rst = Nothing

hth,
Wayne