Ms-access – MS Access fail to recognize correct field width

importms access

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.

Best Answer

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

Manually edit the columns in access. (fixed width).
Import the data into excel and use the "text to columns" feature to split the fields. Then import to sql.
Edit the file with a text editor like notepad++ and insert a delimiter between columns.
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.

Related Solutions

Sql-server – Fixed length text file insert into SQL table

As mentioned in the comments above, your time would be much better spent using SSIS for this task. Here's a recent video post that discusses handling multi-line record data files much like yours.

Since you're okay with spinning your wheels, let's try to find an answer anyway--if you have the data in a single row, you have somewhere to start. My next step would be to apply a split function. Here is an excellent article about the popular options for such a function. You will need to choose or adapt a function to handle (n)varchar(max) if your files exceed the standard limits. You'll just use your single row of data as the input parameter, and the carriage return as the delimiter.

Once your data is broken into rows, you should be able to liberally apply substring() to slice out your columns for each row type.

This probably won't perform well, but if you need to show a proof of concept to get access to SSIS, it might suffice.

Import CSV to MySQL – Handling Long Text Fields with Line Breaks

According to the MySQL docs, you can set both the field and line delimiters in the LOAD DATA statement:

[{FIELDS | COLUMNS}
    [TERMINATED BY 'string']
    [[OPTIONALLY] ENCLOSED BY 'char']
    [ESCAPED BY 'char']
]
[LINES
    [STARTING BY 'string']
    [TERMINATED BY 'string']
]

That should enable you to import the TEXT data. Just be careful in choosing your delimiters!

Best Answer

Related Solutions

Sql-server – Fixed length text file insert into SQL table

Import CSV to MySQL – Handling Long Text Fields with Line Breaks

Related Question