How to fix Source tilde delimited file in order to import

[iren]

I have tilde delimited txt file. I am trying to import it into MS Access 2002. I got a system message that not all records were successfully converted because of data errors and referred to the error file with records #

My questions are the followings:

1.Does it mean that bad records were excluded from the imported file or they are still in file?

2.How can I fix them? I mean…I can find a number of the record in source file, but how I know the column? It is quiet cumbersome since it is not a fixed width file….

Is there any easy way of doing it?

Thank you in advance,

Iren

 

[xlbo]

1: No - records will not have beeb loaded into the table

2: Best bet is to load the text file into excel using the text import wizard. Then do an export from access of the table you loaded into. Compare the 2 and where cells do not match in excel, that is the record that was not loaded

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[iren]

Geoff, thank you for your response which is very helpful. I I wonder ,however, if that would be a huge file which Excel would cut due to # of records limitation....Would it be any way to overcome it?

 

[ousoonerjoe]

Go take a look at UltraEdit. It's perfect for cases like this. No record limits and you can put it into Column mode based on a specified delimiter.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[iren]

I s UltraEdit a software or is it an option in Excel?

 

[ousoonerjoe]

UlraEdit is a 3rd party software tool that I feel is worth every penny (about $50 US). In most situations i have run into, it will handle most of what Excel can't in terms of Fixed/Delimited files.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[macropod]

Hi iren,

For this sort of work, you can load the data onto more than one Excel worksheet. Check out

http://support.microsoft.com/kb/120596/en-us

for a macro to do the job.

Cheers

[MS MVP - Word]

 

[iren]

Geoff,

I just tried to apply #2

"2: Best bet is to load the text file into excel using the text import wizard. Then do an export from access of the table you loaded into. Compare the 2 and where cells do not match in excel, that is the record that was not loaded "


How can I export from Excel to Access? I do see Import, but I do not see Export!

 

[xlbo]

click in the table

Tools>Office Links>Analyse with MS Excel

alternatively, select all rows in the table. Copy. Go to excel and paste

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[iren]

Geoff, thank you so much!
I realized one more problem. When I import text tilde delimited file into Excel, I get Headings like the very first row. Try as I might I was unable to find an option in EXCEL importer which allowed me to have headings as real headings. How can I do it? Could you please give me a hand?

Thank you very much in advance

 

[SkipVought]

"allowed me to have headings as real headings."

What does that mean?

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[iren]

Sorry for my poor English. What I am trying to say is that I need a file that has a header row like:

field 1 field 2 field 3
sss ddd gggg

Rather than having header under A, B, C, D etc:

A B C
field1 field2 field3
sss ddd ggg

 

[xlbo]

If you do Tools>Office Links>Analyse with Excel, the headers for the file should be returned in row 1

Don't worry about the A, B, C etc - they are just the column headers in excel - your "real" column headings should appear beneath them. you can turn them off under Tools>Options>Show column headers but there's very little point - it just makes it harder to reference where you are on the spreadsheet

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ousoonerjoe]

In your spreadsheet, select the entire 2nd row. Then from the menu bar, select "Window" then click <Freeze Panes>. This will keep the header row on top at all times.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.