Importing Text Files into SQLServer
-
My users will be uploading a comma-delimited text file that I need to check for format (I do not have control over the file they create) and consistency with my existing data. If the file passes the checks I will import the file into the database otherwise, I'll tell them to go back, fix the file and try again. I can upload the file, perform the checks (once it's in a table) and report the errors. Here's the question. How do I import a tex file of an unknown format (what I need to check) Since this is web-based how do I accomodate multiple-users (i.e. I can't import into one temp file 'cause users will step all over each other) Thanks David
-
My users will be uploading a comma-delimited text file that I need to check for format (I do not have control over the file they create) and consistency with my existing data. If the file passes the checks I will import the file into the database otherwise, I'll tell them to go back, fix the file and try again. I can upload the file, perform the checks (once it's in a table) and report the errors. Here's the question. How do I import a tex file of an unknown format (what I need to check) Since this is web-based how do I accomodate multiple-users (i.e. I can't import into one temp file 'cause users will step all over each other) Thanks David
You might want to use DTS (distributed transaction server) to do a bulk insert from your file. You can even set DTS to run at a later point in time too. Check the SQL Server documentation, I don't remember exactly how DTS works, but there is a VBScript object for it.
-
You might want to use DTS (distributed transaction server) to do a bulk insert from your file. You can even set DTS to run at a later point in time too. Check the SQL Server documentation, I don't remember exactly how DTS works, but there is a VBScript object for it.
I agree, I could use either a BULK INSERT or DTS - if I knew that the uploaded file was going to be in the proper format (i.e. # of columns, datatypes etc.) I beleive for either method we need to stipulate the format. However, since this is what I need to check and I want to tell the user the errors in the file (i.e. You file does not contain the required field "x" or the required field "x" must be of datatype "y")I need a method that will import the data into a table regardless of the format, so that I can check it. I'm thinking of writing an XML file from the comma-delimited upload. Any comments
-
I agree, I could use either a BULK INSERT or DTS - if I knew that the uploaded file was going to be in the proper format (i.e. # of columns, datatypes etc.) I beleive for either method we need to stipulate the format. However, since this is what I need to check and I want to tell the user the errors in the file (i.e. You file does not contain the required field "x" or the required field "x" must be of datatype "y")I need a method that will import the data into a table regardless of the format, so that I can check it. I'm thinking of writing an XML file from the comma-delimited upload. Any comments
If you can't verify that the data has the proper column delimiters and the proper row delimiters then you don't really have a data file, just a text file. You will need to parse it yourself to verify that validity of the data. You could just import each row into a TEXT field in SQL Server and try to parse the data yourself, but you don't know if the user put the proper row delimiter in the file before uploading it, so you may have incorrect data in this TEXT field. Your best bet would be to write a parser yourself and parse the file looking for all the propery column delimiters and row delimiters and report the errors as you find them. If you want to write your own bulk copy program, look at the IRowsetFastLoad interface if you are using OLE-DB, or if your going to program in ODBC, look at the bcp_* functions in the SQL Books Online.