Bulk Insert from Array

Other SQL Server Topics
1

using auto it wrote application to take a text file break it into array and bulk insert into sql table

Problem is now the company that wrote the software that creates the file added in some options where the user can put alpha chars in the column, so now when i go to import the file if i come across one of these type entries it blows up during the insert because the sql table is expecting numeric.

So is there a command i can put in the sql insert command that would eliminate the alpha char and only keep the numeric?

2

How are you using AutoIt to insert into the table ?

3

I am reading the data from the file using the proper offsets, loading the data into an array, only the data i require, then using a loop function i am creating a bulk insert in to sql table. then it does the insert. Just was not sure if there was some sort of function i could add into the insert line that would check the data and if any thing other than numeric eliminate it and change it to zeros.

so lets say the data which is normally numeric is 0006345, with the new features of the program that creates the file it could be LE06345 or it could be 0LE6345, it would depend on the user of the software set it up. But these alpha characters are not there all the time, or at least not now can not say in the future it wont change. So if i can find a way to fix it now then i need not worry in the future as they change their software.

I mean i could change the offsets so that the first 3 characters are not read into the array but if i did that and then the number in that field went above 9999 then i would not have the right numbers.

Hope that is clear.

4

you can create your own function to stip off any non numeric character

Or insert into a staging table with varchar data type isntead of numreric and then from the staging table insert into your actual table after removing the non-numeric

you can refer to this thread on removing non-numeric

Filtering a string

5

Why do you want to throw away the correct data? If the data can now contain alphanumeric data - you need to change your table to accept alphanumeric data.

Modify the table definition from a numeric data type to a varchar data type.