• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 704
  • Last Modified:

"First row of your data file is too large" Text Import

I'm trying to import a 20MB text file. I receive err msg "Can't start the import wizard.  The first row of your data file is too large." when manually importing and Access locks up when I use 'docmd.transfer text'.

The file:
rows have a character length of 4082-4090
 there are no carriage returns, there is no separator between the last character of one record and the first character of the next record.  The last field of each record contains the data "mm/dd/yyyy".

I have created a new text file with just one record in order to create an import spec to use for this file.  no luck.

Any and all help/insight appreciated
0
enite
Asked:
enite
  • 5
  • 4
  • 3
  • +4
1 Solution
 
berg1375Commented:
Have you tried splitting the file up by Field, into several files?

I used a Qbasic file to do a similar action, but I suppose it will only work on the fields that are a set lenght, or have a constant value somewhere in it.

HTH
berg
0
 
eniteAuthor Commented:
berg1375:
that was quick.
each record is between 200 - 220 characters, so roughly 20 records per row.

How do you use the Qbasic file to split yours up?
0
 
eniteAuthor Commented:
Adjusted points to 100
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
eniteAuthor Commented:
I'm done for the day until Wed AM, EST U.S. -- will respond to comments upon return.

thanks
enite
0
 
ozphilCommented:
I would knock up a VB/VBA Access routine to open the file, process every character in it to extract the date-terminated records, and outputting each record to an output file.

I would use the seek() function to process the file.

eg (from VB5 help file)
For files opened in modes other than Random mode, Seek returns the byte position at which the next operation takes place. Assume TESTFILE is a file containing a few lines of text.

Dim MyChar
Open "TESTFILE" For Input As #1      ' Open file for reading.
Do While Not EOF(1)      ' Loop until end of file.
      MyChar = Input(1, #1)      ' Read next character of data.
      Debug.Print Seek(1)      ' Print byte position to Debug
                  ' window.
Loop
Close #1      ' Close file.


Needless to say, more code is needed to detect the mm/dd/yyyy structure etc.
0
 
TrygveCommented:
I go with ozphil. Since you can't detect the lenght of each record by using the normal import routines, you will most likely need to do this from code.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
The problem is no carriage returns.  The wizard is treating the whole file as one record.

  Code is the route to go as suggested.

JimD.
0
 
berg1375Commented:
enite-

I use this file to split a file into two files. The way it works is there are two different kind of records (which depedns on the nineth character), and it just runs through and checks each record, and puts it into the proper file.

CLS
INPUT "enter file name , example 0722.txt"; file$
let sFile$ = file$
OPEN sFile$ FOR INPUT AS #1
OPEN "New1.txt" FOR OUTPUT AS #2
OPEN "New2.txt" FOR OUTPUT AS #3
WHILE NOT (EOF(1))

LINE INPUT #1, x$
IF MID$(x$, 10, 1) = "9" THEN
PRINT #2, x$
ELSE
PRINT #3, x$
END IF

WEND

CLOSE #1, #2, #3

This may work for you, if you have a constant determining factor, or some other way you can code the break between fields.

HTH
berg
0
 
eniteAuthor Commented:
Good Morning and Thanks for the responses:

ozphil and berg1375

right now i'm working on your suggestions  - - i will keep you posted on how this works with my file and process

enite
0
 
ozphilCommented:
berg1375

The 'Line input' assumes there is some sort of carriage return delimeted structure to the file.

From enite's questioons i gather that the only pattern present is the terminateing date field in format mm/dd/yyyy.

So I dont htink the 'Line input' method would work in this instance.
0
 
berg1375Commented:
ozphil-

Like I said I don't know if his file will have the set pattern or any pattern that will let my code, or any similar code work. I just let him know one of the options out there.

berg
0
 
eniteAuthor Commented:
berg1375 & ozphil:

my source data provider was able to change the way the text file is output -- to conform to access text import wizard.  I am able to get the data into access now.

However, you two pointed me to other methods of manipulating and importing text data.

I'll contact customer service to split the points.

Thanks again.

enite
0
 
berg1375Commented:
I am glad you got this resolved.

berg
0
 
ozphilCommented:
The source data provider should get the points. lol.

Seriously though, having looked at the alternatives, you did well to have the data provider change the format.
0
 
ianBCommented:
Hi,

I am answering this question so it can be saved as a PAQ and to split the points.

I have issued separate questions for each expert involved so they claim their points.

Ian
Community Support @ Experts Exchange
0
 
MoondancerCommented:
This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.  If you are an EE Pro user, you can also choose Power Search to find all your open questions.

This is the Community Support link, if help is needed, along with the link to All Topics which reflects many TAs recently added.

http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
http://www.experts-exchange.com/jsp/zonesAll.jsp
 
Thank you,
Moondancer
Moderator @ Experts Exchange
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 5
  • 4
  • 3
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now