Excel/CSV Column with a mix of Integers and strings

Toad Data Point & Toad Intelligence Central
1

I have an Excel file with a column that contains a mix of integers and strings , majority of the rows are just a list of integers(example below).

When I import the file into Toad Data Point, it automatically converts that column to a float data type. As a result, any non-numeric values are being converted to NULL. I’ve tried changing the base Excel formatting and adjusting the format within Toad, but I haven’t been able to prevent this behavior.

Because of this, when I go into Transform and Clean, the column contains a large number of NULL values. Then, when I attempt to load the data into Snowflake, I get errors because Toad is trying to insert a float into a VARCHAR column.

Is there a way to force Toad Data Point to treat this column as text during import so the mixed data is preserved?

Column 1
56865353548
V56467644
0121-5456798
2

Couple of quick questions - are you connecting to the excel spreadsheet as a datasource or importing it into a database (like local storage)? I tried a quick experiment just connecting and got these results:

image
image2176×901 134 KB

Some things you can try:

  1. Import the data into LocalStorage first. When you define an import you have more control over the datatype
  2. Try setting the datatype specifically as 'text' in the excel spreadsheet. I didnt need to but you may have other variables affecting your spreadsheet
  3. If feasible you can add a row of data at the top of your spreadsheet that very clearly has text in this field. This help Toad Data Point see that this should be a varchar field.

If you would like to send me a clean sample of the spreadsheet I can see if I can recreate the error and come up with other solutions.. julie.hyman@quest.com

3

If you are importing the excel file, then you should be able to set the datatype at import:

image
image1320×825 28.9 KB

4

I will give this a try. I was trying to create the excel as Database but it was not connecting because it said it needs a data range name, which i gave, i will try the local storage.

5

Double check the name of the tab in excel. I was finding that long tab names in excel, or ones that non-alpha characters can mess up the automatic 'named range' detection in Toad Data Point

6

This may help -

Import an Excel file into Local Storage in Toad Data Point
In Toad Data Point, customers can use the Import Export Data to import an Excel file into a database, including Local Storage. The Toad Data Point documentation explains that the Import Wizard can import from a single file, multiple files, a SQL query, or SharePoint, and can load data into a new table, an existing table, or a Local Storage snapshot.

image.png1940×1013 59.4 KB

Basic steps

  1. Open Toad Data Point and connect to the target database.
    For a local database, use Local Storage. Local Storage is available in Toad Data Point Professional.

  2. Go to:
    Import/Export on Main Toolbar

  3. On the define data import page - choose source (File) and target (dataSource table)

  4. Review the file preview options.
    For example, select Column names as header if the first row in Excel contains the column names.

  5. Choose the target:

  • A single new table — creates a new local table.
  • A single existing table — appends rows to an existing table.
  • Snapshot — available for Local Storage; creates or overwrites a Local Storage snapshot.1. Select or create the Local Storage database name and table/snapshot name.
  1. Complete the remaining wizard pages and click Finish.
  2. After the import completes, open Local Storage in Object Explorer and use View Details on the table or snapshot to confirm the rows imported.

Helpful resources

7

Now would this work if someone saves a new file under the same name? Or every time I would have to refresh the connect?

8

Tried this, and it does not work, I am getting an error. Not sure how to fix the issue

9

You have some choices here - one is to set up an automation that runs the import. You can then either schedule the automation to run at a particular time (first of the month, for example) or you can kick off the automation as needed. You can also choose to replace the data that you import in or add new data to an existing table.

10

So sorry this is not working for you. You may want to reach out to support (support.quest.com) and open a ticket with them and they can help you resolve your issues.