MS Access fail to recognize correct field width

Question

I am trying to import a very large text file (200 fields +) into MS Access.

The text file is not comma separated. I am not sure if it can be called fixed length, since each field has it own length.

MS Access fail to recognize correct filed width. For example, correct field 1 start from 1 to 6 but MS Access believe it starts from 1 to 46. Am I going to manually correct this problem?

I plan to import to MS Access first and then import to SQL server. Since it does not correctly recognize right width in SQL server wizard

Update:

Sample data. The break line is added by me manually.

Answer 1

Looking at your image the data looks like it is fixed width. You have a few options. You could:

1. Manually edit the columns in access. (fixed width).

2. Import the data into excel and use the "text to columns" feature to split the fields. Then import to sql.

3. Edit the file with a text editor like notepad++ and insert a delimiter between columns.

4. Import the data into SQL into a single column and then use TSQL to parse/substring the data into different fields. It's not a nice job but its doable.

If the data is a mix of fixed width and and delimited you can use a combination of the steps above.

If you have to repeat or automate this process, I recommend you go back to the source of this data and ask them to re-export with a delimiter, grant query access so you can import data directly, or get them to export directly to a database table for you.