Import .dat File To SQL 2000
Oct 17, 2005
My company designs our own banking software written in Progress. Currently twice a year (during our releases) we require our bank customers to run a program on their system that pulls certain data and dumps it to a .dat file. This .dat file was then imported (using another progress program) into a Progress database that our development team created (called stats.db) so we could have access to certain information regarding the customer's software/hardware set-up and utilization of programs,etc. This stats.db is now going away as we have a centralized SQL database for all customer information. The new process will be for our customers to send in their .dat files, they would be forwarded onto me, then I will need to import them into our SQL database. I am struggling in finding a way to do this. Our .dat files contain one field that holds the information, therefore the data is not delimited in such a way that I can just pull it into Excel, Access, CSV, etc. How can I go about pulling data out of these .dat files into a view/table in SQL for importing?
I am at a total loss and have spent hours researching this issue. Any help will be greatly appreciated. Thanks...
View 3 Replies
ADVERTISEMENT
Jul 20, 2005
Hi all,I have a file with an extension of .sdf. I "believe" it is a text fileof some sort but I am uncertain. The source agency hasn't returned anyof my calls so I'm wondering if anyone is familiar with this extension?I'd like to import the file into my database - when I use DTS and chosea text format, regardless of what delimiter I choose, the format isstill really ugly. when I pull it up in a huge text editor, it is hardfor me to tell what it is there.I saw in one of my searches that it could be a comma delimited (it'snot) .. could be a unisys file? I know it's not much information to goon - but where should I start in trying to get this into my databasewithout knowing the format? Any suggestions would be greatlyappreciated.Thanks!Bethany*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Aug 21, 2007
I need help importing a complex xml file using the XML Bulk Load component. I need there to be 2 tables as shown below. I just
cannot seem to figure out how to get this to work with such a complex XML structure. I have shown below my table structure, a
sample of one of the entries of the XML files and what I have so far for my XSD schema. Any help would be great!!!
My Tables:CREATE TABLE [dbo].[WPXML] ( [Part] [varchar] (100) PRIMARY KEY, [BaseVehicle] [int] NULL , [Qty] [int] NULL , [PartType] [int] NULL , [EngineBase] [int] NULL , [EngineDesignation] [int] NULL , [ImageURL] [varchar] (100) NULL , [ThumbURL] [varchar] (100) NULL) GOCREATE TABLE [dbo].[WPPRODUCT] ( [Part] [varchar] (100) PRIMARY KEY , [PartNumber] [varchar] (100) NULL , [BrandID] [varchar] (4) NULL , [BrandDescription] [varchar] (100) NULL , [Price] [varchar] (10) COLLATE NULL , [ListPrice] [varchar] (10) COLLATE NULL, [Weight] [varchar] (10) COLLATE NULL, [Popularity] [varchar] (10) NULL, [OEFlag] [varchar] (10) NULL, [ProductRemark] [varchar] (1000) NULL, [Note] [varchar] (5000) NULL ) GOSample of XML:<App action="A" id="1484266"> <BaseVehicle id= "5899"/> <EngineBase id= "555"/> <EngineDesignation id= "138"/> <Qty>0</Qty> <PartType id= "6192"/> <Part>W0133-1621038</Part> <Product> <PartNumber>W0133-1621038</PartNumber> <BrandID>FUL</BrandID> <BrandDescription><![CDATA[Full]]></BrandDescription> <Price>17.38</Price> <ListPrice>36.60</ListPrice> <Available>Y</Available> <Weight>1.05</Weight> <Popularity>B</Popularity> </Product> <Product> <PartNumber>W0133-1611982</PartNumber> <BrandID>KN</BrandID> <BrandDescription><![CDATA[K&N Filters]]></BrandDescription> <Price>68.78</Price> <ListPrice>105.81</ListPrice> <Available>Y</Available> <Weight>1.80</Weight> <Popularity>E</Popularity> </Product> <Product> <PartNumber>W0133-1626304</PartNumber> <BrandID>ND</BrandID> <BrandDescription><![CDATA[Denso]]></BrandDescription> <Price>22.34</Price> <ListPrice>36.60</ListPrice> <Available>Y</Available> <OEFlag>OEM</OEFlag> <Weight>1.05</Weight> <notes>Notes For This Part</notes> <Popularity>D</Popularity> </Product> <ImageURL><![CDATA[http://img.eautopartscatalog.com/live/W01331621038OES.JPG]]></ImageURL> <ThumbURL><![CDATA[http://img.eautopartscatalog.com/live/thumb/W01331621038OES.JPG]]></ThumbURL> </App>
My XSD Schema Thus Far:<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema"><xsd:annotation> <xsd:appinfo> <sql:relationship name="test" parent="WPXML" parent-key="Part" child="WPPRODUCT" child-key="Part" /> </xsd:appinfo></xsd:annotation> <xsd:element name="App" sql:relation="WPXML" sql:relationship="test"> <xsd:complexType> <xsd:sequence> <xsd:element name="Qty" type="xsd:integer" /> <xsd:element name="Part" type="xsd:string" /> <xsd:element name="BaseVehicle"> <xsd:complexType> <xsd:attribute name="BaseVehicle" type="xsd:integer" sql:field="BaseVehicle" /> </xsd:complexType> </xsd:element> <xsd:element name="PartType"> <xsd:complexType> <xsd:attribute name="id" type="xsd:integer" sql:field="PartType" /> </xsd:complexType> </xsd:element> <xsd:element name="EngineBase"> <xsd:complexType> <xsd:attribute name="id" type="xsd:integer" sql:field="EngineBase" /> </xsd:complexType> </xsd:element> <xsd:element name="EngineDesignation"> <xsd:complexType> <xsd:attribute name="id" type="xsd:integer" sql:field="EngineDesignation" /> </xsd:complexType> </xsd:element> <xsd:element name="ImageURL" type="xsd:string" /> <xsd:element name="ThumbURL" type="xsd:string" /> <xsd:element name="Product" sql:relation="WPPRODUCT" sql:key-fields="Part" sql:relationship="test"> <xsd:complexType> <xsd:sequence> <xsd:element name="Part" type="xsd:string" /> <xsd:element name="PartNumber" type="xsd:string" > </xsd:element> <xsd:element name="BrandID" type="xsd:string" > </xsd:element> <xsd:element name="BrandDescription" type="xsd:string" > </xsd:element> <xsd:element name="Price" type="xsd:string" > </xsd:element> <xsd:element name="ListPrice" type="xsd:string" > </xsd:element> <xsd:element name="Weight" type="xsd:string" > </xsd:element> <xsd:element name="Popularity" type="xsd:string" > </xsd:element> <xsd:element name="OEFlag" type="xsd:string" > </xsd:element> <xsd:element name="ProductRemark" type="xsd:string" > </xsd:element> <xsd:element name="Note" type="xsd:string" > </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element></xsd:schema>
View 15 Replies
View Related
Sep 16, 2007
How can I import an mdf file or sql script in ms sql 2000 or
ms sql 2005, also how to do the same if it is am aceess database? Thank youDarlene
View 1 Replies
View Related
Nov 2, 2007
I am trying to import an Excel file - when I pick the file I get the message "Could not open file for reading. Close any other application that may be locking the file."
I have verified that the file is not open - I have even rebooted the machine - still the same message - what am I doing different?
Please advise.
Thanks
View 2 Replies
View Related
Oct 3, 2007
Hi All,
I am trying to import data via DTS from a CSV file. I have the "empty" tables already created with proper column names. Now, I have a subset data of each of these tables, which I am trying to import. I am facing problem in that, there is a datatype conflict between the source (CSV file) and destination (table already in the DB). All the data in the CSV file appears to be of DBTYPE_WSTR, where as in the table it is different (some are DBTYPE_WSTR, some are DBTYPE_DATE, and so on). Is there a way that I can import data successfully? This has become a work stoppage issue now. I had to actually go for this approach of creating the empty tables first and then importing data because, the backup file was very very large and could not be copied to our domain. Please help me out in this.
Thanks a lot.
Mannu.
View 4 Replies
View Related
Feb 23, 2005
i want ot import data from excel .xls file to sql server 2000
into an existing table.
should i use some stored procedures or else
View 5 Replies
View Related
Mar 2, 2005
Hi,
I do not know if it can be done:
how to import an Oracle dump file into SQL server ?
That dump file contains the content of a table, excported from Oracle.
Is there a way or another to import it to SQLServer 2000 ?
Thanks
View 2 Replies
View Related
Jul 20, 2005
Hello,I am receiving a text file that is produced from a mainframe that isout of my control. I am attempting to find a (hopefully clean) way toimport it into a SQL Server database in an automated fashion. I amnot really concerned about how many tables it requires or what theschema looks like as long as the data remains related and ends up inits respective fields (I will probably use scratch tables for this).The data is given to me in a format that is meant to be printed outand read by human eyes (in a text file). The format looks somethinglike this:Begin File:-------------------------------------------------------------------------------1234 1234 1234 1234 XYZ Company 01/01/2003.......More stuff related to XYZ company for a couple of lines ..............(this stuff can easily be parsed by position).......MCARD VISA AMEX DISC-------------------------------------------------------------------------------TOTAL 11111.11 4444.44 5555.55 30.01TRANS FEE .20 .20 .15 .15TRANS AMOUNT 2222.22 888.89 833.33 4.50DISC .0165 .0165 .0365 .0355-------------------------------------------------------------------------------ANOTHER HEADER............More stuff related to XYZ Company................End File:Well, this isn't the exact format, but just an example. The point isthat all of the data in each column is related and should end up inthe same record which is related to the parent record of XYZ Company(or all in a single record in a single table if that is the closest Ican get).Also, the rows are not always present. For example, if TRANS FEEdoesn't apply to anything in the row, then the entire row willcollapse and TRANS AMOUNT would be the next line after TOTAL.I was looking at the bcp utility and dts, but dts doesn't seem to havethe performance capabilities (or reliability for that matter) I amlooking for. Bcp seems like it might work if there is some advancedformatting commands that I can't find in the documentation - Anyone?The best I can come up with is to use a high level language such as C#or VB.NET to parse the text file into another text file that is commadelimited, and then use the bcp utility (or bulk insert) to import itinto SQL Server where I can then use TSQL to manipulate it how I want.I am trying to eliminate the high level language parse and just gostraight from file to database. Does anybody know an easier route?TIA
View 1 Replies
View Related
Jul 20, 2005
I must import some exemplary file to database (MS Srrver 2000) ofcourseusing procedure Transact SQL.This file must:1.Read the xml file2. Create table3. Import this date from xml file to my databasePs. I create procedure who File xml imports to base, but unfortunately sheonly schedule when earlier create a table or table is created.So I need (Ithink) create such mini parser in language transact SQL.Does someone have some ideas?For every help Thanks==== example file xml ===========================================<root><Cust><IDosoby>1</IDosoby><Imie>Lukasz</Imie><Nazwisko>Przypadek</Nazwisko></Cust><Cust><IDosoby>2</IDosoby><Imie>Dariusz </Imie><Nazwisko>Mroz</Nazwisko></Cust><Cust><IDosoby>3</IDosoby><Imie>Tomasz</Imie><Nazwisko>Kolo</Nazwisko></Cust></root>================================================== =========--Luk
View 3 Replies
View Related
Oct 16, 2006
I am attempting to import data from Microsoft Access databases to SQL Server 2000 using the DTS Import/Export Wizard. I have a few errors.
Error at Destination for Row number 1. Errors encountered so far in this task: 1.
Insert error column 152 ('ViewMentalTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 150 ('VRptTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 147 ('ViewAppTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 144 ('VPreTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 15 ('Time', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Could you please look into this and guide me
Thanks in advance
venkatesh
imtesh@gmail.com
View 4 Replies
View Related
Jan 12, 2006
Hi all,
when trying to ímport files to our database server from a client, I keep getting an error:
- Validating (Error)
Messages
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source_txt" (1).
(SQL Server Import and Export Wizard)
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Data Conversion 1" (175).
(SQL Server Import and Export Wizard)
... doing the same import when logged on the server, hasn't been giving me any errors, how come. I can from my client without trouble import tables from other DB servers but when ever it is files it won't do it.
I tried as mentioned in other threads rerun setup to re-install SSIS, but as it was already installed it wouldn't re-install. My next move would be to make a clean install, but not sure it would help, as I think this is a buck.
best regards
Musa Rusid
View 1 Replies
View Related
Jan 29, 2003
Guys.
This has been an issue for me. It happens once in a while.
I am importing a File from a shared folder thru DTS package. Once in a while the DTS package fails due to the following error.
"Error Opening Data File: Process cannot access the file because it is being used by another process"
Is there anyway I can specify that the file is going to be opened in read only, so that the DTS will not fail?
Any other solution/suggestion?
-MAK
View 1 Replies
View Related
Sep 1, 2006
I've got a csv file (75000 records) with 5 columns, one is a date of birth column which shows as an 8 digit long integer (yyyymmdd). I've tried sql dts but I just don't know how to show this column in sql as a date field in dd/mm/yyyy format. Can anyone please help???
View 4 Replies
View Related
Aug 25, 2005
I have several hundred .csv files that have a specific cell that I need to get into a SQL table.
These files are named after the date on which they were created...ie 8252005 would be todays date.
Im looking for a way to import this cell to SQL... the same cell in each file....
Thanks for any help
View 4 Replies
View Related
Jul 23, 2005
Hello,I am trying to import a data from a DB called Reference Manager. Itwas sent as an XML file.How do I import it? I only have the client tools.Thanks,Tmuld.
View 1 Replies
View Related
Mar 9, 2007
I'm trying to migrate some tables from 2000 to 2005. I used the wizard in SSIS to create a package bringing them in.
The first database I did this on it worked great, in no time.
However, I tried this on another database and I get a LOT of these:
Information: 0x4004800C at {0417E15E-7306-4DAD-BEDD-1FDB17390EB0}, DTS.Pipeline: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 2 buffers were considered and 2 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
Now, some things to note:
1. I'm trying to do about 170 tables at once - although none of them are bigger than a MB
2. Copying files between these 2 servers is also taking a long time
Is it too many tables at once? Is it the actual pipeline between the servers? The message above seems to indicate that it is a problem local to the machine, and maybe our file-copying problem is just another issue entirely.
View 3 Replies
View Related
Mar 15, 2006
Thanks in advance. What is maximum SQL Server database (*.mdf) file size with SQL Server 2000 as part of Microsoft Small Business Server 2000? (Database files were limited to 10 GB in SBS 4.5 with SQLServer 7.0... has this changed?).
View 1 Replies
View Related
Sep 21, 2006
Im trying to import tables, stored procedures and data form another sql
2000 instance on my network to my local. After i go
through the steps, it ends up creating hte tables, but none
of the data is pulled over
View 1 Replies
View Related
Dec 20, 2006
I have SSMSexpress which does not include the Tasks/Import Data...Is there an easy way to do this without the full SSMS?I have SQL Server 2005 express for developement which I like alot. Can I possibly load SSMS without installing the entire SQL Server 2005 product?
PS I am almost a complete idiot with SQL Server DBs and I use Visual Basic, not C#
thanksMilton
View 1 Replies
View Related
Mar 10, 2005
Help needed!
I am a final year uni student and as part of my project I've developed a .Net site with a SQL Server 2000 backend.
This week a virus hit our house and my PC got infected (despite anitvirus and firewall!). Anyway, I have had to format my hard disk and reinstall everything.
My site is back in the wwwroot folder and looks fine, trouble is I need to get the database back up and running.
Before I wiped the disk I took the .mdf and .ldf files from the data file in the SQL Server folder in Program Files.
How can I get my database backup and running.
Ive tried creating a new database (with the same name as the .mdf and .ldf files) and replacing the default created files with the copies I saved but this didnt work.
Any help would really be appreciated as I have to demo this to my lecturers soon and my whole grade is riding on it! (no pressure)
Matt
View 5 Replies
View Related
Jan 7, 2005
Hi I have a database in DB2 and i would like to import this data into sql server 2000. How can i do that?
Thanks.
View 5 Replies
View Related
Dec 28, 2003
Hi,
I'm trying to import a Paradox 8 db into SQL Server 2000 using DTS but the list of data sources in the connections dialog only caters for version 5.x or older. Does anybody know how to get the data source for v8?
If not, is there any other quick and efficient way to import a paradox 8 db as I have to import around 15 of them to do.
Thanks
View 4 Replies
View Related
Sep 19, 2005
I need to import data in several hunderd excell spreadsheets to a sql 2000 table. What function should I use? Do I need to create a table in sql with the same columns as the excell file and then run a query? Iam pretty new to sql, so please be gentle
View 4 Replies
View Related
Mar 12, 2007
Hi,
I have developed an application in .NET 2005 using sql server 2005.
This is the enhancement to the existing application.Now whats the problem is, one of the customer wants his old database, which is in sql 2000 server with the new database(sql 2005 server). So, how can i import sqlserver 2000 database into sqlserver 2005 database.
Its very urgent plz.........
Regards,
Kishore
View 1 Replies
View Related
Oct 22, 2007
hi,
i try to Import and export options in sql server 2000 but the fatal error is coming "Could not create instances of an DTS Package".
View 3 Replies
View Related
Mar 3, 2008
How can import sybase db (sql any where) to sql server 2000 ?
sybase can be imported to sql server how ?
I try to import but there is no option to import sybase db
by tool -> data transformation services -> import / export data
Regards
Mateen
View 2 Replies
View Related
Apr 17, 2007
I need to import some excel files in the Database via DTS. The problem is, that I don't know the names of the Excel Sheets (there are multiple sheets in the excel file). Is it possible to get the names of those sheets? (via activeX or SQL Query or anything, but from a DTS package)
View 1 Replies
View Related
Aug 30, 2006
I've got a situation in whcih I have to import PDF and MP3 files into SQL Server 2000 table.
Thanks,
YL
View 3 Replies
View Related
Dec 28, 2005
I want to import XML file as the '@doc' value when I execute 'sp_xml_preparedocument', many thanks!
View 1 Replies
View Related
Sep 28, 2001
Hello every one
i unstalled my sql server and installed it again, and i want the old database. so how i can i import the old database file .mdf, ldf here. i tried to copy this 2files in to data folder, but its not showing database files in the server.
please help me out,i havent taken any backup before uninstallation
with regards
kittu
View 1 Replies
View Related
Jan 2, 2002
Im looking to write a DTS script to import a CSV file into an existing table. I have made sure that all columns correspond. I ideally want to create a stored procedure and a variable to be entered as the file name.
How can I do this?
View 1 Replies
View Related
Apr 15, 2004
How do you import a dbf file to sql and dump it into a table please help . iam totaly new at this.
View 2 Replies
View Related