Data mapping and Importing app for SQL server

Hello,

Our company often receives data from outside sources to add to our application. This data is usually provided to us in Excel, CSV, XML, etc. The files that we receive usually have different columns from the columns in our database, so we have to map these columns to our table structure to import.

I'm looking for an application that will easily allow me to load up the data file (whatever type it may be), expose the columns in the data file, allow me to map these columns in our SQL server, then import the data. I know that this can be done as DTS, however I'm looking for alternatives. Does anyone have any recommendations?

Thanks in advance.

I just dont have a solution to your post but i have the same problem. I just want to get notifications on reply event of this thread. Hopefully some one will answer you and i will benefit.

data import using wizard

Hello,
I'm having problem importing a simple comma delimited text file into a
simple 3 fields table. I used the DTS wizards, I got to pick my source text
file, but on the following step, it gave me the following error message.
"Invalid delimited data text qualifier, must be followed by a column
delimiter",
I don't understanding what it means, my text file looks fine, is a simple 3
columns text file. Please help this dumb beginner.
Any good beginner book on SQL 2000 I should read'
Thanks in advance for any helpFor sql server - SQL SERVER IN 21 DAYS (BASICS)
FOR dts basics - step by step by Microsoft
Regards
R.D
"SQLbeginner" wrote:

> Hello,
> I'm having problem importing a simple comma delimited text file into a
> simple 3 fields table. I used the DTS wizards, I got to pick my source te
xt
> file, but on the following step, it gave me the following error message.
> "Invalid delimited data text qualifier, must be followed by a column
> delimiter",
> I don't understanding what it means, my text file looks fine, is a simple
3
> columns text file. Please help this dumb beginner.
> Any good beginner book on SQL 2000 I should read'
> Thanks in advance for any help|||I mean SQL SERVER DTS - STEP BY STEP( see if possible, google it for free
soft copy)
Regards
R.D
"SQLbeginner" wrote:

> Hello,
> I'm having problem importing a simple comma delimited text file into a
> simple 3 fields table. I used the DTS wizards, I got to pick my source te
xt
> file, but on the following step, it gave me the following error message.
> "Invalid delimited data text qualifier, must be followed by a column
> delimiter",
> I don't understanding what it means, my text file looks fine, is a simple
3
> columns text file. Please help this dumb beginner.
> Any good beginner book on SQL 2000 I should read'
> Thanks in advance for any help|||>> I don't understanding what it means, my text file looks fine, is a simple
Perhaps you might want to verify the input data, since the error message
points to a badly formatted source file. If the file size is smaller,
consider loading into a spreadsheet and see if it looks OK.
For beginners, SQL Server Books Online trumps anything out there.
Anith|||Thanks guys!
I finally figured out something though. Following is a small sample of my
text file that created from Excel using the (csv comma delimited) option:
"12345","Smith","Jones"
"45678","Lewis", "Shank"
On the DTS 'Select File Format' page, it has the following:
File type: ANSI
Row Delimiter: {CR} {LF}
Text Qualifer: Double Quote {"}
But it does not recognize the double quote as the Text Qualifer, if worked
beautifully when I stripped out the double quote. Is there a way to get
around that because 90% of the files I receive have the double quote'
Thanks!!
"Anith Sen" wrote:

> Perhaps you might want to verify the input data, since the error message
> points to a badly formatted source file. If the file size is smaller,
> consider loading into a spreadsheet and see if it looks OK.
>
> For beginners, SQL Server Books Online trumps anything out there.
> --
> Anith
>
>|||You could write a simple asp page where you can browse to the text file
(using file system object) you receive. Then write a loop that reads each
line and loads it into a temp variable and uses the replace function to
replace all quotes with empty space ("").
Or you can get the people who send you the text file to get their shit
straight and stop inserting double quotes. But the business usually doesn't
work with the techies, they usually want you to work around their horse shit
.
Anyways, the code for that should be simple. If you need help, look up code
on the File system object, opening/reading a text file line by line, and the
replace function.
RG
SQLbeginner wrote:
>Thanks guys!
>I finally figured out something though. Following is a small sample of my
>text file that created from Excel using the (csv comma delimited) option:
>"12345","Smith","Jones"
>"45678","Lewis", "Shank"
>On the DTS 'Select File Format' page, it has the following:
>File type: ANSI
>Row Delimiter: {CR} {LF}
>Text Qualifer: Double Quote {"}
>But it does not recognize the double quote as the Text Qualifer, if worked
>beautifully when I stripped out the double quote. Is there a way to get
>around that because 90% of the files I receive have the double quote'
>Thanks!!
>
>
>[quoted text clipped - 6 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200509/1|||There's a space after the comma after Lewis.
Bruce Dunwiddie
http://www.csvreader.com|||That was my typo, there should not be a space anywhere.
"shriop" wrote:

> There's a space after the comma after Lewis.
> Bruce Dunwiddie
> http://www.csvreader.com
>

Data import

I am importing data from text files nightly and I am wondering if there is a way to have SQL recognize whether the text file has been updated before the job runs. The text files come over from our Unix box and every once in a while the transfer fails, so when this happens I don't want the SQL jobs to run because I just get duplicate data and not updated data. I don't know if this is possible, but I am just wondering. Thanks.Are you saying that if the process fails, the old files still exist. Do the filenames change - and what about date/time for the files ? Also, what would happen if you deleted or moved the files when you had successfully imported the data ? And what are you using now to import the data ?|||Yes, the files are overwritten nightly. I am using a DTS package to import from the textfile. I thought about just deleting the files after the import, then the jobs would just fail if no files were there, but I just wondered if there was a way for SQL to recognize the date on the file before running the job.|||You can do either - run the dts based on file existence or on file date/time. I would add a file existence step in your dts package and a file move/delete step after the import was successful.|||How would I word the existence step based on date/time? Thanks|||You can create an activex script task using the filesystemobject.|||Have you worked with the filesystemobject - if not I can show you an example.|||Just created it and it seems to be working, but I have it setup only to see if the file exists, if it doesn't then the package fails, which is what I want. Is there a way to have it check for file date and fail if it is not correct instead of just if it exists?|||You can use the File object and read the DateCreated property. You can create the File object using the GetFile method of the filesystemobject.

Data Import

Hi
I am importing data into a column and want to change all the values to read
in percentages i.e. 15 should become 0.15
How do I do this during an import? or how do I update the column afterwards?How are you performing the import? If you use DTS you should be able to
enter the expression in the source query of a Transform Data task.
The UPDATE is straightforward:
UPDATE YourTable SET col = col / 100
David Portas
SQL Server MVP
--|||Hi,
At the time of import data from table ,divide by that column with 100 and
then import the value,I think this will solve your problem.
for ex:
select price/100 as 'price' from titles
Regards
Arindam
"Jaco Wessels" wrote:

> Hi
> I am importing data into a column and want to change all the values to rea
d
> in percentages i.e. 15 should become 0.15
> How do I do this during an import? or how do I update the column afterward
s?
>
>|||use * .01 rather than /100 just in case you have a zero entry which may caus
e
a divide by zero error.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Jaco Wessels" wrote:

> Hi
> I am importing data into a column and want to change all the values to rea
d
> in percentages i.e. 15 should become 0.15
> How do I do this during an import? or how do I update the column afterward
s?
>
>

Data Format issue while importing data from Excel to SQL

hi

when i m importing data from excel to Sql using DTS the column which has text content was not imported as same in excel sheet. whereas a special character is appearing in between the lines. the text field contains multiple lines but the conetent is imported in single line .

ex:

ARIZONA
ALABAMA
STATE

but i m getting imported

as :

ARIZONA ALABAMA STATE

How to Format a single column while importing?

Regards

Raj

That is a very special and custom task which has to be coded on your own, there are no built in functionalities for this nested entities.

Jens K. Suessmeyer.

http://www.sqlserver2005.de