Importing .TXT file into Access 2010

[Strikertom]

Hi people,

I am having difficulty importing a large txt file into my database, due to the first column containing a * prefix. normally i would just go through the document and delete it, but this file is quite large at over 100k records.

Is there anyway of importing this file in access 2010 and telling access to ignore the first column?

Thanks in Advance.
Tom

 

[namliam]

Well you can (re) write the file quite easily

    Dim myLine As String
    Open "file" For Input As #1
    Open "filetemp" For Output As #2
    Line Input #1, myLine
    Do While Not EOF(1)
        Write #2, myLine
        Line Input #1, myLine
    Loop
    Close #1
    Close #2

Or even go manual import on this using above or simular... Just need to tweak the reading and writing, etc.

On the other hand if you want to skip the entire column you can define that quite easily in the ImportSpecification which you can create when you manually import any text file. Just click the "Advanced" button during the (manual) import of the file and "fix" your import spec

 

[CJ_London]

yes

when you create an import spec (by importing manually from the external data tab) you are prompted for the delimiter, whether the file contains headings etc. To the botton left you will see the advanced button. Click on this to open the form below and in the field information section you can click on skip to prevent the field from being imported. If the failure is due to the field being interpreted as numeric and then failing when an * is found, you can also modify here to import as text, then use a query to do the necesary corrections once imported

Recommend you then save the spec (same form) with a recogniseable name for use in transfertext

 

[Strikertom]

Thanks for the info guys, I will try those methods, for now I have imported the file into excel 2010, then deleted the first column with the * field, then saved that as a txt document and imported that, and that seemed to work.

Thanks again.
Tom