Importing a text file or exel file causing errors both in csv and excel formats

[world33]

Hello,

Can anyone please help me identify what is the issue I have been facing for a couple of days now while trying to import a text file (named Update), or the same text file converted into an excel file, into an Access new table?

With the csv text file I get the following error message: "The field AcademicDivisions contains a start position of 37809. The maximum start position is 32767"

In the case of the Excel file I get the following error: " The search key was not found in any record"

I tried to search for a solution to both errors online and tried, in addition to compacting and repairing the database, the following:

1) For the text file I tried to skip some fields while importing; I started with a few at the time (for example all long text fields) and ended up skipping all fields but still getting the same error message. I also checked if there was no space in the fields headings as someone has suggested for solving this issue.

2) For the excel file I tried to calculate the total number of characters for each row to see if it exceeded the maximum number of characters. In the attached excel file, in a separate sheet, I calculated that the number of characters for each field and the total number of characters for each record to import. The maximum number of characters reached by one record is 26,500. Could that be the problem?

If not can anyone please help me understand how to solve the issue?

I attach both copies of the same data with two different extensions as above.

Thanks a lot!

 

[The_Doc_Man]

The .CSV file problem is simply that the length of your input lines exceed the addressing range of the conversion routine. Your lines are too long.

In trying to convert some other data to an Access file, you are limited to a maximum record size of about 4000 characters (actual limit also depends on whether using extended or standard character sets.) You can only have up to 255 fields and 4000 characters total. You cannot convert data sets larger than this without doing some serious VBA operations.

In a nutshell, your data records are too long for Access, which has record-length limits on what it can manage.

 

[Uncle Gizmo]

I looked into importing CVS and had an interesting discussion about it with Richard @The_Doc_Man and Colin @isladogs in this thread here:-

Joining large tables together

I have a small problem. I keep running into error >>>> Record is too large. (Error 3047)

www.access-programmers.co.uk

 

[world33]

Ok thanks a lot. I appreciate your feedback

 

[CJ_London]

You haven't said how you are trying to import - are you using transfertext, transferspreadsheet? Or whether linking or importing (although latter is implied)

If you are going to be normalising the data, this should not be a problem - just do it using sql - the basic query based on your text file is

SELECT *
FROM (SELECT * FROM [TEXT;DATABASE=D:\Dev\1_temp;HDR=Yes].Update.csv)  AS txt;

which works for me - change the path to suit and can be converted to an insert/update query

Normalised it looks like you would have tables for universities, degree types plus linking table to assign degree types to universities, similar for fees etc

Or just save the query and reference as you would an imported or linked table - tho' I would recommend normalising

 

[Pat Hartman]

Even for a table, there is a hard limit on the number of columns and the total character length.

If you have to read a flat file that does not contain CrLf to separate records or whose records are wider than the maximum length, you have to use FSO or Line Input to read the file rather than one of the transfer methods. Read the entire file as a blob and then split it into records and columns.

 

[CJ_London]

you have to use FSO or Line Input

my example works - using sql

OP's example contains 116 columns. If there were more than 255 columns, then perhaps you would need to use FSO or line input

 

[Pat Hartman]

my example works - using sql

But does it work for records longer than 4,000 characters? That is the point.

 

[CJ_London]

It works for the OP’s file which is stated to have a maximum character count of 26,500 for one record- I showed a screenshot of the last few columns to demonstrate it works

if the OP has not provided representative data- what would be the point?

edit: I’ve just checked the file and record character counts vary from 26,500 down to 690

 

[Pat Hartman]

Well, it wouldn't be the first time we've found documentation errors. Maybe, because the "table" is actually linked to a flat file, it doesn't conform to all the rules. Did you try to append the data to a table? Were you able to add rows with more than 4000 characters?

 

[CJ_London]

No My recommendation was to normalise the data if importing or just reference the query when required

you have access to the data, can use the sql I provided, so you can try yourself if you want

At least it is a solution rather than an alternative direction to go

 

[world33]

Thank you for all your help and useful input.
To answer some questions:
I tried to import the table not to create a linked table or through VBA. I used the standard Import Wizard with both a csv file and and an excel.
For what is worth I managed to create a new access table with only the headings first then copied and pasted the data a few columns at the time from the excel table. I know it sounds like a primitive and unorthodox way of creating a table but it works for now.
I will try the sql code that CJ_London provided. Thank you for that!

 

[ebs17]

As @CJ_London wrote, if you import this file as is into a database table, it's just garbage because you can't really work with it.
In database work you need atomic information and structures in data (keyword data modeling and normalization).

But it may be that you just want to look at the data without any further claims. But the link shown is also sufficient, an import only has a symbolic effect in the direction that something has been accomplished.