Text importer

[gulhaugen]

Trying to import a file into a table with all varchar2's. It seems like the text importer is trying to be smart, and not always enclose these fields with '.

If I'm missing something, please tell me. If I'm not missing something, could you add an option to not auto-detect the type?

 

[Marco Kalter]

Can you email me a small example?

 

[marybeth]

I had the same problem with varchar2's. I saved a spreadsheet as a .csv and noticed that if the column contains all numbers then a single quote will be placed around it, but it doesn't do the same with regular letters. This is obviously a bug. However, I had to do a substring on the column and managed to put quotes in by using the SQL function on the Data to Oracle page. I did this for each varchar2 column: substr('#', 1, 30) (in this case I only wanted the first 30 chars).

 

[gulhaugen]

I can't send the file, but I we noticed it again, on a different file. The field in question has no datatype specified from the file, and changing it to String makes everything work okay. When I come accross this with a file I can send, I will come back to you.

 

[mikeatphs]

I am having a problem with the Text Importer and zip codes. U.S" ZIP Codes are numeric but Canada's are Alpha Numeric. So we have the target table defined for zip code as VARCHAR2. The Text File I am importing has the Zip Code's enclosed with double quotes. When I use Text Imported and open the text file and point to the target table, the format of the zip code is defined as numeric by Text Importer? It appears that Text Importer takse a sample of the data and uses it to determine the attributes of the input text file. Because the first 100 or so zip codes on the text file are numeric (even though they are enclosed with double quotes) it builds insert script with a numeric attribute for the zip code element. When a Canadian zip code is encountered (120,000 records later) the import stops becuse the input zip code is not numeric.

Why doesn't the Text Importer just allow the field to be VARCHAR2 because it is enclosed by quotes?

thanks

 

[Marco Kalter]

The default data type is determined from the first 100 records. If this default data type is incorrect, you can simply override the fieldtype and set it to "String".

 

[marybeth]

How do you handle strings that have an apostrophe inside of it? The importer is taking my string and placing single quotes around it, which is correct, but isn't placing an additional single quote within the string. For example, I'm trying to import the word gov't as a string and it is doing this: 'gov't' instead of: 'gov''t'

 

[Marco Kalter]

That would be a bug. We'll check it out and will fix it.

 

[gulhaugen]

The problem was of course, Excel. When you save it to a tab file, it strips off the tabs at the end if there is no data, so it ends up as "jagged" tab file. Workaround if you are so unlucky to have data in excel, is to add a column to the far right, with something in it.