Toad World® Forums

Cannot Import csv


#1

I got around the memory error, but now I get this trying to import a csv into
Oracle:

Error parsing data based on your input, please try other the file format and
file encoding.
The file Assignment_List.csv is not text based and cannot be processed.

Yes the grammar is funny in the actual message.

I tried several different encoding formats, UTF-7 UTF-8, UTF-32, Canadian French
etc, but no dice. It is a straight up text file, comma separated with “quoted
strings”.

Help!
Regards,
Tom


#2

So I created a test table:
create table test(
column1 varchar2(20)
,column2 varchar2(20)
)
Then I created a test file with notepad:
“column1”,“column2”
“this”,“blue”
“that”,“red”
“other”,“black”

The insert worked correctly.

My problem is that I have an Access Database where I append some files and
restructure the columns, then I export a query result in csv.
That csv cannot be read by TDA. I tried exporting as txt, csv, excel, nothing
seems to work.
I will try some other combinations, the file is about 100k rows.


#3

Ok, figured this out.
My file contains french characters and there doesn’t seem to be a file encoding
option TDA will recognize the characters. I tried French Canadian (DOS) and
other France, French encoding formats, but TDA will not recognize these
characters.

Try inserting this into the test table:
“column1”,“column2”
“François Frenchman”,“Blue”


#4

Sorry for the inconvenience, I created CR 77,777 for this issue.

Thanks.


#5

After painstakingly removing the french characters, TDA finally recognizes the
file, but when attempting to execute the insert, now I get:
Thread (13) Import Started [27/08/2010 6:15:43 PM]
Thread (13) Processing “Assignment_List.csv” into “STAGE.WL_ASSIGNMENTS”
Thread (13) Importing 1 out of 1 files
Thread (13) Reading from file Assignment_List.csv
Thread (13) Error importing data, please check file format options: Object
reference not set to an instance of an object.

I restarted TDA, made sure I was focused in the appropriate Schema, rebuilt the
table, refreshed the views, but still no dice.

The new import wizard features look nice, but I can’t wait to actually use it.


#6

Tom,

Sounds like a real mess. We changed the underlying component that we use to
parse Excel and csv . There is an attribute to extend the character sets which
Michael mentioned he will do in CR77,777. But that does not explain the error
you are getting below. I’d like to speed up the fixes here. Can you send
me the original exported file by private email? I’d like to run it through
the debugger and see all that is happening.

Debbie


#7

Still no luck.

I tried every single file encoding option.

Please try to insert this into Oracle:

create table test(
varchar2(60)
);
insert this csv:
“François Larivé”
;


#8

[ Attachment(s) from Debbie Peabody included below]

I am able to do this import with csv and xls file input.

I changed to French Canadian regional settings and this works fine also. What
else could be different in your environment? I assume you are using French
Canadian regional settings. Do you have any different file encoding on your
input file?

I have attached the files I am using. Can you try importing them and see if the
result is any different.

Also, what version of the Beta are you using?

Debbie


#9

Regional settings?
You mean I have to change the computer’s regional settings?
There must be a more elegant way. I cannot change the server’s regional
settings.


#10

No. I am not saying to change your regional settings. I am trying to find out why my inserts works and yours don’t. If I can’t produce the error on my side I can’t fix anything. I have always said that if we can produce an error in our development environment it is as good as fixed. Producing the users issue sometimes is the hardest part.

Can you send me your insert file? It might seem silly but perhaps there is something different in the file, like the encoding or something…


#11

Well, I used TDA to extract from SQLserver as a csv, with double quoted strings,
then tried to import that file into oracle.
I will PM you a sample of the csv where it fails to read.


#12

Not sure if I found the bug or not.

The original file was 285meg. When I split it into 4 parts, it seems to have
loaded correctly. So the problem may be memory management related. I had more
than 1gig of RAM available and gigs of page file.

Please try to load a 285meg file from a 4gig RAM XP setup.

Regards,
Tom


#13

Tom, I had a similar issue in TOAD-SS with inserts statements running out of
memory, which was addressed by David Christian.

I have attached and email which cover what I did to resolve this using BULK
Loads for loading CSV files which were just as large as your files. But just
in case the attached email doesn’t make it through Yahoo, here is the
bottom line below my signature.

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary

David I agree !!!

Toad-SS should say something to the order of : ‘Application Memory
limitation has been reached, please limit the size of the file your are loading
and processing’

Additionally, I had to abort Toad-SS 5.0.1 because after it failed on memory
resources, for my chair Toad-SS should not have to be restarted for this reason.

I abandoned insert statements for the obvious reasons…

Bulk Load is my friend. After I decided to use the ‘ ~ ’ as my
Fieldterminator because I had to many address fields with ‘ , ’
embedded in the field.

Working well for now.

BULK INSERT Administration.dbo.HF_TEST_DDCC_3A_UPD

– FROM ‘C:\Extract\Insert_For_DDCC_FirstChoice.txt’ – SQL07

– FROM ‘C:\Extract\Insert_For_DDCC_Liberty.txt’ – SQL07

– FROM ‘C:\Extract\Insert_For_DDCC_TEN.txt’ – SQL07

FROM ‘C:\Extract\Insert_For_DDCC_Texpo.txt’ – SQL07

– FROM ‘C:\Extract\Insert_For_DDCC_Vantage.txt’ – SQL07 – done ~no errors

WITH

( FIRSTROW = 2 ,

FIELDTERMINATOR = ‘~’ ,

ROWTERMINATOR = ‘\n’

)

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary
image002.jpeg


#14

Tom, I had a similar issue in TOAD-SS with inserts statements running out of
memory, which was addressed by David Christian.

I have attached and email which cover what I did to resolve this using BULK
Loads for loading CSV files which were just as large as your files. But just
in case the attached email doesn’t make it through Yahoo, here is the
bottom line below my signature.

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary

David I agree !!!

Toad-SS should say something to the order of : ‘Application Memory
limitation has been reached, please limit the size of the file your are loading
and processing’

Additionally, I had to abort Toad-SS 5.0.1 because after it failed on memory
resources, for my chair Toad-SS should not have to be restarted for this reason.

I abandoned insert statements for the obvious reasons…

Bulk Load is my friend. After I decided to use the ‘ ~ ’ as my
Fieldterminator because I had to many address fields with ‘ , ’
embedded in the field.

Working well for now.

BULK INSERT Administration.dbo.HF_TEST_DDCC_3A_UPD

– FROM ‘C:\Extract\Insert_For_DDCC_FirstChoice.txt’ – SQL07

– FROM ‘C:\Extract\Insert_For_DDCC_Liberty.txt’ – SQL07

– FROM ‘C:\Extract\Insert_For_DDCC_TEN.txt’ – SQL07

FROM ‘C:\Extract\Insert_For_DDCC_Texpo.txt’ – SQL07

– FROM ‘C:\Extract\Insert_For_DDCC_Vantage.txt’ – SQL07 – done ~no errors

WITH

( FIRSTROW = 2 ,

FIELDTERMINATOR = ‘~’ ,

ROWTERMINATOR = ‘\n’

)

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary
image002.jpeg


#15

Tom, I had a similar issue in TOAD-SS with inserts statements running out of
memory, which was addressed by David Christian.

I have attached and email which cover what I did to resolve this using BULK
Loads for loading CSV files which were just as large as your files. But just
in case the attached email doesn’t make it through Yahoo, here is the
bottom line below my signature.

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary

David I agree !!!

Toad-SS should say something to the order of : ‘Application Memory
limitation has been reached, please limit the size of the file your are loading
and processing’

Additionally, I had to abort Toad-SS 5.0.1 because after it failed on memory
resources, for my chair Toad-SS should not have to be restarted for this reason.

I abandoned insert statements for the obvious reasons…

Bulk Load is my friend. After I decided to use the ‘ ~ ’ as my
Fieldterminator because I had to many address fields with ‘ , ’
embedded in the field.

Working well for now.

BULK INSERT Administration.dbo.HF_TEST_DDCC_3A_UPD

– FROM ‘C:\Extract\Insert_For_DDCC_FirstChoice.txt’ – SQL07

– FROM ‘C:\Extract\Insert_For_DDCC_Liberty.txt’ – SQL07

– FROM ‘C:\Extract\Insert_For_DDCC_TEN.txt’ – SQL07

FROM ‘C:\Extract\Insert_For_DDCC_Texpo.txt’ – SQL07

– FROM ‘C:\Extract\Insert_For_DDCC_Vantage.txt’ – SQL07 – done ~no errors

WITH

( FIRSTROW = 2 ,

FIELDTERMINATOR = ‘~’ ,

ROWTERMINATOR = ‘\n’

)

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary
image001.gif


#16

Tom, I had a similar issue in TOAD-SS with inserts statements running out of
memory, which was addressed by David Christian.

I have attached and email which cover what I did to resolve this using BULK
Loads for loading CSV files which were just as large as your files. But just
in case the attached email doesn’t make it through Yahoo, here is the
bottom line below my signature.

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary

David I agree !!!

Toad-SS should say something to the order of : ‘Application Memory
limitation has been reached, please limit the size of the file your are loading
and processing’

Additionally, I had to abort Toad-SS 5.0.1 because after it failed on memory
resources, for my chair Toad-SS should not have to be restarted for this reason.

I abandoned insert statements for the obvious reasons…

Bulk Load is my friend. After I decided to use the ‘ ~ ’ as my
Fieldterminator because I had to many address fields with ‘ , ’
embedded in the field.

Working well for now.

BULK INSERT Administration.dbo.HF_TEST_DDCC_3A_UPD

– FROM ‘C:\Extract\Insert_For_DDCC_FirstChoice.txt’ – SQL07

– FROM ‘C:\Extract\Insert_For_DDCC_Liberty.txt’ – SQL07

– FROM ‘C:\Extract\Insert_For_DDCC_TEN.txt’ – SQL07

FROM ‘C:\Extract\Insert_For_DDCC_Texpo.txt’ – SQL07

– FROM ‘C:\Extract\Insert_For_DDCC_Vantage.txt’ – SQL07 – done ~no errors

WITH

( FIRSTROW = 2 ,

FIELDTERMINATOR = ‘~’ ,

ROWTERMINATOR = ‘\n’

)

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary
email.eml (80.1 KB)


#17

Running out of memory while processing imports has to do wtih how how many rows we process at a time. The default is 500. But if the row length is large or you have many columns, 500 rows is too many. It should have to do with the size of the file as this is streamed in in chunks.

In TDA 2.6 you can change the row buffer size in the GUI. In 2.7 we took that out and it can be manually edited in the import template.

In the last BETA posting we add a simply change of changing the row buffer size to 100 if there are more than 50 collumns.

I recently entered an enhancement for 3.0 to write a more intelligent algorithm to determine the buffer size based on actual row length.

Debbie


#18

Debbie, that is a very good idea, Toad-SS hopefully will be doing the same.

Please advise how you are suppose to deal with very large files like insert
statements or CSV just loading into the editor?

I had to use Textpad to split the file before I decided to abandon the inserts
and just use the CSV with bulk load…

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary
image002.jpeg


#19

Debbie, that is a very good idea, Toad-SS hopefully will be doing the same.

Please advise how you are suppose to deal with very large files like insert
statements or CSV just loading into the editor?

I had to use Textpad to split the file before I decided to abandon the inserts
and just use the CSV with bulk load…

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary
image001.gif


#20

Debbie, that is a very good idea, Toad-SS hopefully will be doing the same.

Please advise how you are suppose to deal with very large files like insert
statements or CSV just loading into the editor?

I had to use Textpad to split the file before I decided to abandon the inserts
and just use the CSV with bulk load…

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary
image002.jpeg