Problems with Data Import

[Robbo1974]

Hi all,

I have been trying to import some address records to a table from a word document. The problem is that the addresses have been formatted for labels.

I have tried saving the document as a .txt file and then importing from there, but the conversion from .doc to .txt doesn't delimit the field properly and the import wizard places all of the data in a single field.

I can not get the data in any other format and I need to get this information onto my db, without having to enter it manually (there are 350+ records).

Any advice would be gratefully recieved. Thanks in advance, Iain

Robbo ;-)

 

[AKAC00L]

Probably a daft question, but have you tried just copying and pasting the data into the table?

 

[heythere]

try taking the data from word into excel, then into access, or
data into excel, into delimited .txt to access.

excel tends to be most useful here

 

[Robbo1974]

Hi guys,

Unfortunately neither solution works for me.

AkaCool's suggestion involves cutting and pasting around 2000 - 2,500 individual data items and I just don't have the time to do it that way. I'd be at the office til midnight and believe me - I'm not that dedicated!

heythere's suggestion just leaves me with the same problem - all of the data lumped into one column.

I really need to get this sorted if possible so any suggestions will be gratefully recieved. Please help! Thanks, Iain Robbo ;-)

 

[mac318]

If you are familiar with MS-DOS basic, use the following Open statement along with "Line Input #filenumber, varname" to access each data element (field). You can then write each data element to the appropriate field in your table.

"Open pathname For mode [Access access] [lock] As [#]filenumber [Len=reclength]"

I use the scheme to import clients data from old DOS program files into my Windows database.

mac

 

[Robbo1974]

Mac,

Thanks for the suggestion - sounds useful, but I have no knowledge of MS-DOS basic. Could you suggest a routine in VBA? Robbo ;-)

 

[Frink]

Hallo,

You do not mention how the txt file is delimited, which does make it rather hard to propose a solution.

To read a number from the front of a string (regardless of delimitation) use the val function.
Expr1: Put Val([StringFieldName]) in a query and it will return all the digits from the front, or zero if there is no number at the front. It works for decimal and negative numbers as well.

- Frink

 

[Robbo1974]

Hi Frink,

Sorry for the lack of info. The .txt file delimits with the  character if I save as a raw .txt. For each carriage return in the original document there is one of these  characters.

Having thought about this overnight, I think that the best way to go about this is:

Import the data to a new table, it should look something like this:

1st company name
contactname
address line 1
address line 2
address line 3
address line 4
null
null
2nd company name
contactname
address line 1
address line 2
address line 3
address line 4
null
null
3rd company name
etc, etc

(All in one column)

Then, use VB to run through the records, counting as it goes. The first value is appended to a new table column 1, the second value into a new table column 2 etc until there is a null value, when the counter resets and the next value is then appended to new column 1, next new column 2 etc, etc

I don't know if this is possible, am I barking up the wrong tree? My VBA is admittedly quite limited
Robbo ;-)

 

[Frink]

Hallo,

Try looking at the thread:
Splitting Messages into Fields
On this Forum. It does something similar to what you want.

The trouble you will have is the delimiting character.
Either edit the file in Word and replace the s with commas, or replace the "," in the InStr function in my code with a ""

- Frink

 

[mac318]

Robbo,

This example uses the Line Input # statement to read a line from a sequential file and assign it to a variable. This example assumes that TESTFILE is a text file with a lines of data separated by carriage returns.

Dim TextLine
Open "TESTFILE" For Input As #1 ' Open file.
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, TextLine ' Read line into variable.
Debug.Print TextLine ' Print to Debug window.
'Replace the above line with code to place each
'TextLine (variable holding the field) in the
'appropriate Access field.
Loop
Close #1 ' Close file.

mac