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


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

enter image description here

  • Is there any delimiter at all? Are the strings contained within quotation marks? – Randolph West Oct 21 '16 at 21:55
  • No. No. There are only data. – Jill Clover Oct 21 '16 at 22:31
  • Please show us an example. Your comment makes no sense. – Randolph West Oct 21 '16 at 22:34
  • @RandolphWest Sorry for not being clear. Sample data added. Thanks. – Jill Clover Oct 21 '16 at 23:31
  • How big is the file? – Sir Swears-a-lot Oct 25 '16 at 9:10

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.