Toad World® Forums

Input String was not in a correct format ERROR

hello

I have started getting this error message recently while importing Excel (.xlsx) files into ORACLE db and creating temp table. today for example I am creating a table from a one column excel and data type in Oracle table is nvarchar (100). It all should work. but it does not. Error message is not very descriptive.

Please advise.

thank you.

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/36/6567.Toad-error.docx:550:0]

What version of Toad are you using? Also version of your Oracle server. Could you paste the import log here so I can see where the import failed. If you can provide a sample input xlsx file and your import template, I will try reproduce it myself.

Thanks!

thank you Kiki. I am using version Toad Data point 3.5. Oracle - 11. Not sure how to attach sample file. Please advise and thank you again.

4:48:45 PM Thread (1) Connecting to :, LMICHEL.

4:48:45 PM Thread (38) Import Started [8/14/2014 4:48:45 PM]

4:48:45 PM Thread (38) Processing “DSR 473 KoreanWorks_IDNOs.xlsx” into a new table LMICHEL.TEST_EXTRACT

4:48:45 PM Thread (38) Dropping Table

4:48:45 PM Thread (38) Creating table LMICHEL.TEST_EXTRACT

4:48:45 PM Thread (38) CREATE TABLE LMICHEL.TEST_EXTRACT (“ISBN_ISSN” NVARCHAR2(100))

4:48:45 PM Thread (38) Importing 1 out of 1 files

4:48:45 PM Thread (38) Reading from file DSR 473 KoreanWorks_IDNOs.xlsx

4:48:45 PM Thread (38) Change Schema to LMICHEL

4:48:45 PM Thread (38) Inserting row 500 into table LMICHEL.TEST_EXTRACT

4:48:45 PM Thread (38) Inserting row 1000 into table LMICHEL.TEST_EXTRACT

4:48:45 PM Thread (38) Inserting row 1500 into table LMICHEL.TEST_EXTRACT

4:48:45 PM Thread (38) Inserting row 2000 into table LMICHEL.TEST_EXTRACT

4:48:46 PM Thread (38) Inserting row 2500 into table LMICHEL.TEST_EXTRACT

4:48:46 PM Thread (38) Inserting row 3000 into table LMICHEL.TEST_EXTRACT

4:48:46 PM Thread (38) Inserting row 3500 into table LMICHEL.TEST_EXTRACT

4:48:46 PM Thread (38) Inserting row 4000 into table LMICHEL.TEST_EXTRACT

4:48:46 PM Thread (38) Inserting row 4500 into table LMICHEL.TEST_EXTRACT

4:48:46 PM Thread (38) Inserting row 5000 into table LMICHEL.TEST_EXTRACT

4:49:06 PM Thread (38) Import canceled

4:49:06 PM Thread (38) Failed importing 0 rows on step of Input string was not in a correct format.

4:49:06 PM Thread (38) Import canceled and inserted rows were rolled back.

4:49:06 PM Thread (38) Input string was not in a correct format.

4:49:06 PM Thread (38) Import Finished [8/14/2014 4:49:06 PM]

4:49:06 PM Thread (38) Import time: 00:00:20.7630761

Please go to rich format and click Options tab on top of description pane to attach the files. Or you can email me to yake.li@quest.com directly. Thanks!

3731.UploadFile.png

thank you so much for your willingness to assist. I have sent an email with an attachment to the email address you have provided.

We apologize, but Kiki is currently on vacation. She will respond once she gets back.

I am currently having this same problem. I have a field that should be varchar but when changed to that datatype during import process, no data will import (it is the first field). If I change it to integer datatype, the table does import with all data. However I must have it be varchar. Same error message. Here is copy of import log.

1:49:28 PM Thread (17) Import Started [12/30/2014 1:49:28 PM]

1:49:28 PM Thread (17) Processing “A1c Registry Removal List 12-14 UPLOAD TO TOAD.xlsx” into a new table U0104665.DM_EXC_DEC_2014

1:49:28 PM Thread (17) Dropping Table

1:49:28 PM Thread (17) Drop table U0104665.DM_EXC_DEC_2014 failed, error: ORA-00942: table or view does not exist

1:49:28 PM Thread (17) Creating table U0104665.DM_EXC_DEC_2014

1:49:28 PM Thread (17) CREATE TABLE U0104665.DM_EXC_DEC_2014 (“PATIENT MRN” NVARCHAR2(50) NOT NULL,

“PATIENT NAME” NVARCHAR2(59) NOT NULL,

“Date Removed” TIMESTAMP,

“Provider” NVARCHAR2(50),

“Not DM” INT,

“Deceased” INT,

“Moved/ No longer with practice” INT,

“Managed by Specialist” INT,

“Change PCP” NVARCHAR2(65),

“Community Clinic” NVARCHAR2(50), PRIMARY KEY (“PATIENT MRN”))

1:49:28 PM Thread (17) Importing 1 out of 1 files

1:49:28 PM Thread (17) Reading from file A1c Registry Removal List 12-14 UPLOAD TO TOAD.xlsx

1:49:29 PM Thread (17) Change Schema to U0104665

1:49:29 PM Thread (17) Inserting row 448 into table U0104665.DM_EXC_DEC_2014

1:49:29 PM Thread (17) Continuing import regardless of the error: Input string was not in a correct format.

1:49:29 PM Thread (17) 448 rows from file A1c Registry Removal List 12-14 UPLOAD TO TOAD.xlsx were processed.

1:49:29 PM Thread (17) A total of 0 out of 448 rows were imported to table DM_EXC_DEC_2014.

1:49:29 PM Thread (17) Failed importing 0 rows on step of Input string was not in a correct format.

1:49:29 PM Thread (17) Imported rows were rolled back.

1:49:29 PM Thread (17) Input string was not in a correct format.

1:49:29 PM Thread (17) Import Finished [12/30/2014 1:49:29 PM]

1:49:29 PM Thread (17) Import time: 00:00:00.7360000

I think it is pretty lame that this question has still not been answered. Hopefully someone can help me!

This is a data driven error. The conversation was taken offline through email as Yake needed to get the excel file to help the original user. If you will attach your file or send to Yake’s email we can look into your specifics. P.S. what type of database are you connecting to?