Importing a text file

[Kurs]

I'm trying to import a text file which has 232050 records and 80 fields. I selected the - fixed width option while importing. After the importing was complete, there are only 5 fields populated. I'm not sure where I might be going wrong but I am following all the steps for importing a text file.



Any help/suggestions would be greatly appreciated.

Thanks!

[June7]

This is not a comma delimited file? All columns are actually the same width? Want to attach a sample of the text file, maybe a 100 lines?

[ssanfu]

I'm trying to import a text file which has 232050 records and 80 fields. I selected the - fixed width option while importing. After the importing was complete, there are only 5 fields populated. I'm not sure where I might be going wrong but I am following all the steps for importing a text file.

Any help/suggestions would be greatly appreciated.

Thanks!

Maybe your text file is not a fixed width file. Are the table fields the correct type for the data?
Is the file extension ".txt" or ".csv"?

Care to post a line of the text file - without sensitive data?

[ssanfu]

This is not a comma delimited file? All columns are actually the same width? Want to provide a sample of the text file, maybe a 100 lines?

Drat!!!!! You're first AGAIN!!!

[Kurs]

Thank you all for your responses.
1. It is not a comma delimited file but there are spaces between each field. Also, the columns are not of the same width.
2. I'm afraid I cannot attach of sample of it since it is a confidential data. (but i wish i could though :P)
3. The file extension is .txt

Steve, I didn't get your second question- "Are the table fields the correct type for the data?"

Thanks again!

[ssanfu]

Thank you all for your responses.
1. It is not a comma delimited file but there are spaces between each field. Also, the columns are not of the same width.
2. I'm afraid I cannot attach of sample of it since it is a confidential data. (but i wish i could though :P)
3. The file extension is .txt

Steve, I didn't get your second question- "Are the table fields the correct type for the data?"

Thanks again!

How about changing the data.... ie if it is a SSN, change it to 123-45-6789

Steve, I didn't get your second question- "Are the table fields the correct type for the data?"

If a field type is a long, and the import is trying to enter a text string.... or a NULL, the data won't be saved.

[June7]

Cannot use fixed width parameter. Use space or tab as the delimiter. Are you using the import wizard or VBA code? If VBA, show code. We don't need real data, just structure. Put dummy names and ssn, whatever, into a file. Just a few lines would be enough, enough to adequately represent the dataset.

[Kurs]

The data looks similar to the one in census 2000 ftp raw data (modified) which I have attached. I cannot import this as well.

Thanks again.census2000geo.txt

[John_G]

Hi -

I looked at the text file you posted; you will not be able to use blanks as a delimiter with that, because a) a lot of the fields are blank, and b) at least one of the fields contains blanks (address). From what I can see, the format is fixed width, that is each field is the same width in each record, but it is NOT space delimited. What looks like one string of characters could be many fields adjacent to each other.

If this is the case, where each field is the same width in each record, but some fields are blank, my suggestion would be to read each entire record as one string, and then use VBA to split out the separate fields.

When you said "there were only 5 fields populated" - were the fields populated correctly, and were they the first 5?
When you used the wizard, and specified fixed-width option - did it not ask you where the field breaks were? It should have.

HTH

John

[ssanfu]

I would use VBA and the Mid() function.
Read each line, parse it into variables or an array, create the INSERT query, loop until done.
Can't tell where the columns start and end in some areas of the sample text.

[Kurs]

Thank you for your response, John.
The 5 fields populated are not the first 5.
I'm trying to run it using the fixed width option and wizard. It did ask me where the field breaks were. However, I need to analyze the table and decide on the fields.

This will help me in moving forward.
Thanks again for your help.

[Kurs]

Yes. Got that. It might take quite some time to do that!

Thanks for your help.