enite
asked on
"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
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
ASKER
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?
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?
ASKER
Adjusted points to 100
ASKER
I'm done for the day until Wed AM, EST U.S. -- will respond to comments upon return.
thanks
enite
thanks
enite
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.
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.
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.
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.
Code is the route to go as suggested.
JimD.
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
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
ASKER
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
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
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.
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.
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
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
ASKER
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
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
I am glad you got this resolved.
berg
berg
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.
Seriously though, having looked at the alternatives, you did well to have the data provider change the format.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
https://www.experts-exchange.com/jsp/zonesAll.jsp
Thank you,
Moondancer
Moderator @ Experts Exchange
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.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
https://www.experts-exchange.com/jsp/zonesAll.jsp
Thank you,
Moondancer
Moderator @ Experts Exchange
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