I use SQL server 2005...
I have a tab delimited file which I want to import into my SQL server database.
My sql server table setup is:
CountryID int (autogenerated, identity specification)
CountryName nvarchar(40)
CountryAbbreviation nvarchar(3)
In my tab delimted file I have two columns:
CountryName and CountryAbbreviation
How can I best solve this?
I am using the Bulk Insert command and trying to import a CSV delimited text file into a table and I am having problems with the quote field delimiters ", " The command below works but it takes in all the "" quotes as well and the field delimiter comma , works only if the commas are the separators only. If I have a comma within a address field for example then the data gets imported into the wrong fields. What can I use to identify that the text qualifier is ". I don't see where I can use the bulk insert command to determine this. Is there another command that I can use or am I using this command incorrectly. I thank you in advance for any response or suggestion you may have.
BULK INSERT AdventureWorks.dbo.MbAddress
FROM 'a:mbAddress.txt'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR=',',
ROWTERMINATOR='',
CODEPAGE = '1252',
KEEPIDENTITY,
KEEPNULLS,
FIRSTROW=2)
Here is a sample ascii file I am importing as well you can see that 6330 has a extra comma in the address line.
"AddressAutoID","Memkey","Type","BadAddress","Address1","Address2","Address3","City","State","Zip","Foreign","CarrierRoute","Dpbc","County","CountyNo","ErrorCode","ChangeDate","UserID"
6317,26517,1,0,"1403 W. Kline Ave","","","MILWAUKEE","WI","53221","","",0.00,"MILWAUKEE",79,"",1/25/2006 0:00:00,"admin"
6318,26225,1,0,"501 Dunford Dr","","","BURLINGTON","WI","53105","","",0.00,"RACINE",101,"",1/25/2006 0:00:00,"admin"
6319,20101,1,0,"2115 Cappaert Rd #35","","","MANITOWOC","WI","54220","","",0.00,"MANITOWOC",71,"",1/25/2006 0:00:00,"admin"
6320,23597,1,0,"728 Woodland Park Dr","","","DELAFIELD","WI","53018","","",0.00,"WAUKESHA",133,"",1/25/2006 0:00:00,"admin"
6321,23392,1,0,"7700 S. 51st St","","","FRANKLIN","WI","53132","","",0.00,"MILWAUKEE",79,"",1/25/2006 0:00:00,"admin"
6322,26537,1,0,"W188 S6473 GOLD DRIVE","","","MUSKEGO","WI","53150","","",0.00,"WAUKESHA",133,"",1/26/2006 0:00:00,"admin"
6323,25953,1,0,"3509 N. Downer Ave","","","MILWAUKEE","WI","53211","","",0.00,"MILWAUKEE",79,"",1/26/2006 0:00:00,"admin"
6324,19866,1,0,"10080 E. Mountain View Lake Rd. #145","","","SCOTTSDALE","AZ","85258","","",0.00,"MARICOPA",13,"",1/27/2006 0:00:00,"admin"
6325,25893,1,0,"W129 N6889 Northfield Dr. Apt 114","","","MENOMONEE FALLS","WI","53051-0517","","",0.00,"WAUKESHA",133,"",1/27/2006 0:00:00,"admin"
6326,26569,1,0,"8402 64th Street","","","KENOSHA","WI","53142-7577","","",0.00,"KENOSHA",59,"",1/27/2006 0:00:00,"admin"
6327,24446,4,0,"83 Sweetbriar Br","","","LONGWOOD","FL","32750","","",0.00,"SEMINOLE",117,"",1/30/2006 0:00:00,"admin"
6328,19547,1,0,"4359 MERCHANT AVENUE","","","SPRING HILL","FL","34608","","",0.00,"HERNANDO",53,"",2/8/2006 0:00:00,"admin"
6329,26524,1,0,"264 Lakeridge Drive","","","OCONOMOWOC","WI","53066","","",0.00,"WAUKESHA",133,"",2/10/2006 0:00:00,"admin"
6330,23967,1,0,"3423 HICKORY ST","100 Tangerine Blvd., Brownsville, TX 78521-4368","Texas Phone Number: 956-546-4279","SHEBOYGAN","WI","53081","","",0.00,"SHEBOYGAN",117,"",2/15/2006 0:00:00,"admin"
6331,25318,1,0,"3960 S. Prairie Hill Lane Unit 107","","","Greenfield","WI","53228","","",0.00,"MILWAUKEE",79,"",2/20/2006 0:00:00,"admin"
6332,24446,1,0,"83 Sweetbriar BR","","","LONGWOOD","FL","32750","","",0.00,"SEMINOLE",117,"",2/21/2006 0:00:00,"admin"
6333,26135,1,0,"P.O. Box 8 127 Main Street","","","CASCO","WI","54205","","",0.00,"KEWAUNEE",61,"",2/21/2006 0:00:00,"admin"
I am struggling on using bcp to import data. Here is my steps:
1. I created a Test database on my localhost
2. In the Test database, I created a Test table, the query is here for your convenience:
CREATE TABLE [dbo].[Test](
[id] [int] IDENTITY(1,1) NOT NULL,
[network_group_name] [varchar](128) NULL,
[IP] [varchar](15) NULL,
[OS] [varchar](128) NULL,
[Code] ....
I then create the format file used in bcp:
bcp Test.dbo.Test format nul -c -t, -f C:RXieSQLTest.fmt βT
Here is the format file:
9.0
8
1 SQLCHAR 0 12 "," 1 id ""
2 SQLCHAR 0 128 "," 2 network_group_name SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 15 "," 3 IP SQL_Latin1_General_CP1_CI_AS
[Code] ...
The data file is called 20150902FullTest.rpt and the first couple lines (first line is the header and followed by two rows) are posted here:
network_group_name,IP,OS,App_Name,vuln_name,host_score,recordswritten
Domestic,10.216.56.88,Windows XP SP3,Adobe / Macromedia Flash Player,APSB14-17: Adobe Flash Player CVE-2014-0537 Vulnerability,4350,2015-09-01 09:55:07.720
Domestic,10.216.56.88,Windows XP SP3,Adobe / Macromedia Flash Player,APSB14-17: Adobe Flash Player CVE-2014-0539 Vulnerability,4350,2015-09-01 09:55:07.720
With the format file and the data file, I use the following bcp command:
bcp Test.dbo.Test in C:RxieSQL20150902FullTest.rpt -f C:RxieSQLTest.fmt -T
I got the following error messages:
Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
[Code] ...
I do want to mention here is the rpt file contains three BOM characters EF BB BF at the beginning of the file.
hi all,
While importing a tab delimited file..
it seems ssis interprets the incoming col as 50 chars in length even
though it is far smaller.
any ideas how this could be??
any help would be appreciated.
I have a text file that is delimited by nulls. Any idea on the best way to get this into a SQL Server table?
View 9 Replies View RelatedHello...
I have a problem... When I insert data from a comma delimited
file using this mehod a flat file connection sorting, merge join and
inserting into the database I get "" around all the data!! The
quotes end up around the column names and everything! I had to go
in and manually remove the quotes in the text file to get some of my
data conversions to work. I know there is a better way. How
do I get SSIS to load the data without the quotes? This is an
example of the data in the file:
"1007","1","A","","Congratulations - No Health Violations Found","11/02/2005","1007"
When I remove the quotes I do not have any problems. How do I
do this without modifying the underlying data? Any ideas would be
greatly appreciated!!
Thank you for your help!
SD
How do I do this?
I cannot find any facility like there is a Access for getting external data.
I have a text file I am trying to import to a table. This text file is in a tab delimited format. I am using DTS to import the data to a new table I made. The fields are varchar and are set to allow nulls & allow 8,000 characters per field.
The error I am getting is that the data exceeds the allowed amount (or something like that) in col4.
Now I have checked everything in column 4 and nothing exceeds 5,000 spaces/characters combined. I have checked the entire sheet (in excel) for that fact, and there is not one single column/row/cell that exceeds 5,000 spaces/characters combined.
What the heck could be causing SQL to tell me I am trying to import too much data in one column when there is nothing that even comes close to 8,000 characters & spaces combined?
The problem is that I have (for example) following data
AA AA LH
BB BB ML
CC CC QA
DD DD PS
in space delimited file and i want to populate a database using SSIS and it is not working. Any suggestions will be appreciated.
Dear friends i want to import excel file into sql server 2005 in asp .net using c# can anyone help me?
rgds,RK
Hi all,
I'm using shared sql space on a new provider and am trying to import a database backup file - of the format filename.db.
I cannot figure out how to import this file type using the 2005 express tools - I can connect to the database and create and drop tables alright, but I cannot just import the existing database.
I really haven't got the time on my connection to download the fully blown SQL server trial to be able to do this, so can anyone help me out with any easier solutions?
Cheers, Mike
I want to be able to import data from a text file, into SQL Server 2005, using OPENROWSET. Can you pl give the the syntax for this. What I HAVE IS
select * --into #tmp1
From OpenRowSet
( 'Microsoft.Jet.OLEDB.4.0',
'Text;\ABC.TXT
)
Hi all,
I'm using shared sql space on a new provider and am trying to import a database backup file - of the format filename.db.
I cannot figure out how to import this file type using the 2005 express tools - I can connect to the database and create and drop tables alright, but I cannot just import the existing database.
I really haven't got the time on my connection to download the fully blown SQL server trail to be able to do this, so can anyone help me out with any easier solutions?
Cheers, Mike
Does anyone have any great suggestions on how I can import an online XML file into an SQL 2005 table?
So far I tried to accomplish it with SSIS Data Flow Task where my source is XML Source (Data access mode: XML file location; XML location: URL, Use inline schema = True). This set up properly identified the columns to be imported.
I used Copy Column data flow transformation task to load data to OLE DB destination table that has same structure.
When I run the task it does execute with no errors, however the table remains empty. It looks like I am failing to read the actual data.
Do you have any suggestions? I am willing to go around this approach with stored procs/com/you name it just make it work!
Thanks a lot for your help!
LoveDanger
Hi
I am new to VB, and am looking to write some code to import a delimited (by a ~) .txt file into a SQL server table. It doesn't need to append, just to totally overwrtie the table. Is this possible? I have been looking at the Bulk Insert, but this doesn't seem to be quite right. Can anyone help?
Cheers,
S
I have one column in SQL Server 2005 of data type VARCHAR(4000).
I have imported sql Server 2005 database data into one mdb file.After importing a data into the mdb file, above column
data type converted into the memo type in the Access database.
now when I am trying to import a data from this MS Access File(db1.mdb) into the another SQL Server 2005 database, got the error of Unicode Converting a memo data type conversion in Export/Import data wizard.
Could you please let me know what is the reason?
I know that memo data type does not supported into the SQl Server 2005.
I am with SQL Server 2005 Standard Edition with SP2.
Please help me to understans this issue correctly?
Hi,
I'm trying to deploy my Web site to GoDaddy. They told me I have to export the SQL Server Express database to a comma delimited file and then upload that file. The export procedure is simple in Access but I don't see any way to do it in SQL Server or from Visual Web Developer or Visual Studio.
Also, I can ask them, but I assume I have to export each table separately and also export the ASPNETDB as well.
Thanks for the help
I'm a little stuck here. How can I import a flat tab delimited file into a SQL 2005 Express table? The standard DTS and import features are not there :-(Thanks,Casey
View 1 Replies View RelatedHi,
In my project i have to import csv file into DB, i have written a C# windows application as below...
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;namespace FedExDataAnalysis
{
public partial class Form1 : Form
{public Form1()
{
InitializeComponent();
}protected string[] arrayValues;
//string row = "";
// int cnt = 0;private void Form1_Load(object sender, EventArgs e)
{string path = "c:\2008-01-09_01-00_FedExInv_232316837.csv";
importCSV(path);string path1 = "c:\2008-01-10_05-18_FedExInv_232316837.csv";
importCSV(path1);
}public void importCSV(string path)
{string row = "";int cnt = 0;
using (StreamReader sr = new StreamReader(path))
{while (sr.Peek() >= 0)
{
cnt = cnt + 1;
row = sr.ReadLine();arrayValues = row.Split(',');if (cnt > 2)
{if (arrayValues.Length > 0)
{
insertrecord();
}
}
}
}
}public void insertrecord()
{string str;
int Recordaffected;string inserted = "no";
string constr;SqlConnection conn;
SqlCommand cmd;
constr = "Data Source=I07;Initial Catalog=JuiceApp;User ID=sa;Password=dj$ql1736ju1c30ca1a";conn = new SqlConnection(constr);
conn.Open();
try
{str = "insert into xt_importcsv (Bill_to_accountno,Invoice_date,invoice_number,Original_ammountDue,Current_Balance,express_or_ground_trackingid,";
str = str + "transportation_charge_ammount,net_charge_ammount,shipment_date,actual_weight_amount,rated_weight_ammount,recipient_name,recipient_company,";str = str + "recipientaddress_line1,recipient_city,recipient_state,recipient_zipcode,recipient_country,shipper_name,trackingid_charge_description,trackingid_charge_ammount,";
str = str + "trackingid_charge_description1,trackingid_charge_amount1,trackingid_charge_description2,trackingid_charge_ammount2,trackingid_charge_description3,trackingid_charge_ammount3,";str = str + "trackingid_charge_description4,trackingid_charge_ammount4) values ('" + arrayValues[0].ToString().Replace(""", "") + "','" + arrayValues[1].ToString().Replace(""", "") + "','" + arrayValues[2].ToString().Replace(""", "") + "','" + arrayValues[4].ToString().Replace(""", "") + "','" + arrayValues[5].ToString().Replace(""", "") + "','" + arrayValues[8].ToString().Replace(""", "") + "','" + arrayValues[9].ToString().Replace(""", "") + "','" + arrayValues[10].ToString().Replace(""", "") + "','" + arrayValues[13].ToString().Replace(""", "") + "','" + arrayValues[18].ToString().Replace(""", "") + "','" + arrayValues[20].ToString().Replace(""", "") + "','" + arrayValues[25].ToString().Replace(""", "") + "','" + arrayValues[26].ToString().Replace(""", "") + "','" + arrayValues[27].ToString().Replace(""", "") + "','" + arrayValues[29].ToString().Replace(""", "") + "','" + arrayValues[30].ToString().Replace(""", "") + "','" + arrayValues[31].ToString().Replace(""", "") + "','" + arrayValues[32].ToString().Replace(""", "") + "','" + arrayValues[34].ToString().Replace(""", "") + "','" + arrayValues[80].ToString().Replace(""", "") + "','" + arrayValues[81].ToString().Replace(""", "") + "','" + arrayValues[82].ToString().Replace(""", "") + "','" + arrayValues[83].ToString().Replace(""", "") + "','" + arrayValues[84].ToString().Replace(""", "") + "','" + arrayValues[85].ToString().Replace(""", "") + "','" + arrayValues[86].ToString().Replace(""", "") + "','" + arrayValues[87].ToString().Replace(""", "") + "','" + arrayValues[88].ToString().Replace(""", "") + "','" + arrayValues[89].ToString().Replace(""", "") + "')";cmd = new SqlCommand(str, conn);
Recordaffected = cmd.ExecuteNonQuery();if (Recordaffected == 1)
{inserted = "yes";
}
}catch (System.Exception ex)
{Console.WriteLine(ex);
}
finally
{
conn.Close();
}
}
}
}
It is working fine.. It is importing data into DB. But the problem what m facing is , in the code above m using the split function and assigning the row of the CSV file into arrayValue. the prob is if the arrayValue has two entries lk: Name: "john,Mikel " it will split it separately and insert into the next field in the DB... M not getting how resolve this... Please help...
Thanks,
Chai.NV.
hi all,i have a table called Employee in sql server 2005 as shown beloweid ename esal001 john 5000002 lina 4000 I need to fill the above table from xml file(Emp.xml) as shown<Root> <Employee> <eid>003</eid> <ename>rose</ename> <esal>2000</esal></Employee><Employee> <eid>004</eid> <ename>sam</ename> <esal>6000</esal></Employee></Root> plz try to help me i am in need or give me any helpful suggestions thanks in advance
View 6 Replies View RelatedI hope this is the right forum for my question.
I'm developing a website for a Prepaid Calling Cards distributor. Each of the cards they sale have a list of the countries the card is good for. I need to import this data into my countries_rates table. The file they are giving me is an excel file that contain 3 colums (fields)
1- Country-Name
2- Rate
3- Card_$_Price
these files contain aproximaly 400 rows so it will be a hasle to have to insert it manually every week.
In my web application I need to create a form where the user will select the card from a dropdownlist and then find the excel file to be imported for that card.
I would like to know how do I do that with Visual Studio 2005, SLQ 2005 and C#
please direct me to some links where I can learn how to do this or please send me some code snips I can see how is done.
Tia
Charles
Hello everyone,
I have exported data from few tables from my old sql server 7.0 database. Now, I need to import those data into new database which is on SQL Server 2005 Express. How do I do that in 2005 express server? Any idea?
Or, is there any better way to import data for selected tables into new database?
Thanks,
Ujjaval
Can someone please help me.
I need to import a csv fiel to sql server and I know that the column delimiter is
and the newline delimiter is but I don't know what the rowterminator is or the fieldterminator. How can I import the file into an empty table in an existing database.
Any suggestions would be greatly appreciated.
I'm really new to the whole database deal (as well as VB.net) - specifically with the capabilities surrounding VB and SQL Server 2005. My question is open to any recommendations...
What I have is an application that a user uses to create 'new' products. They are presented a form to enter the information regarding the product they wish to create. They enter the details of the product and also locate an image that represents the product, too. Currently, this application saves the product information (including binary image data) into a SQL Server 2005 Express Edition database. This application and database reside on a client pc. What I need to do is to be able save updated and newly created product data into a file of some sort. That file will make its way to a memory stick (USB) and then be transported to a 'field' machine. Quite simply, what is the best way to do this? Are there walk-throughs on this sort of thing? The target database is also SQL Server 2005. I thought I'd post this question on here to get the best design ideas... Any help would be greatly appreciated.
~javasource
I am trying to import into sql server from a .csv flat file i made in excel. The problem is the Primary Key. It is set to auto increment.
When i leave an empty column i get:
"Directcopyxform conversion error:destination does not allow null"
when i just omit the column entirely, i get a wrong datatype error because it basically tries to copy all the columns 1 shift to the left
How am i supposed to represent and auto PK in .csv file. Thanks
Dear All,
I am writing a procedure to import daily the customer excel file to SQL server 2000, I managed to do that where the excel file will be imported directly to the SQL server after creating the new data table, & then I need to read the created table & import it row by row to my original data table.The problem:
I. The original excel file has the following:a. a protection passwordb. The contents has two merged headers (which effecting the import procedure)c. And last line is a totals line
Before importing the file I have manually to remove (a β b & c)!!
The Solution:
II. I am trying to find a way to do the above points automatically inside the project.
III. Also I thought of importing the excel file to a DataGrid first then:a. Let the user approve the file contents &b. Remove manually point (I.b.) above (I donβt now how yet, need to try it).c. Then import the DataGrid the the SQL server.
I think I prefer solution (III), any suggestions are highly appreciated
BR
Hello,I am trying to load a text file into SQL Server but the text file seems to be in an unsual format that SQL Server is having a problem reading. I have tried the various options for delimited and fixed file formats.Any ideas would be appreciated.Sample of the file: Dn DCHB;β¦ b` DCHCVDR SMGSWP04JOB08748SMA 704DSEARS VDR SWEEP 4 RDSSWSM REPTPROCSTEP1 V-1 &Γ &Γ BRTA_UA46 200508082345079999 BANNER PAGE ;β¦ b` DCHCVDR SMGSWP04JOB08748SMA 704DSEARS VDR SWEEP 4 RDSSWSM REPTPROCSTEP1 V-1 &Γ &Γ BRTA_UA46 20050808234507 420 ;β¦ b` DCHCVDR SMGSWP04JOB08748SMA 704DSEARS VDR SWEEP 4 RDSSWSM REPTPROCSTEP1 V-1 &Γ &Γ BRTA_UA46 20050808234507 425 ;β¦ b` DCHCVDR SMGSWP04JOB08748SMA 704DSEARS VDR SWEEP 4 RDSSWSM REPTPROCSTEP1 V-1 &Γ &Γ BRTA_UA46 20050808234507 440
View 1 Replies View RelatedCan anybody please give an example of how to import data from an Excel file to SQL Server in a VB Application using DTS.
I am particularly facing problems creating the connection for the Excel file. An example for that would be aprticularly helpful.
Thanks in adv,
Rahul.
Hello
I am writing program in VC++ through SQl-DMO calls.My problem is when i when i tranfer(import) a text file(comma seperated) into SQl server through a SQl-DMO method called ImportData which is a method of Bulk copy object.Its is not able to convert the data field in the text file to corresponding value datetime in SQl server whereas other data types are working perfectly.
This is the record i need to convert:
90,MichaelB,Wintriss,Inspection,Paper,11,Job101,1, {ts '2000-12-10 15:54:56.000'},D:public233 and 247233.mcs,
and this is the date field
{ts '2000-12-10 15:54:56.000'}
Whereas if i export a table in SQl server in Binary mode and then import the file back it works but when do it as text it gives the above error
Pls help me in this i would be very thankful to you.
Note: I am using SQL Server 7.0 version
Regards
Jitender Singh
I got the following error when importing file to sql server 2005. I reinstalled the MDAC as well.
Please help.
TITLE: SQL Server Import and Export Wizard
------------------------------
An error occurred which the SQL Server Integration Services Wizard was not prepared to handle.
------------------------------
ADDITIONAL INFORMATION:
Exception has been thrown by the target of an invocation. (mscorlib)
------------------------------
The connection type "EXCEL" specified for connection manager "{E3861233-443A-439A-BB8D-2777D84DB343}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
({C3728B0D-B172-4246-9B14-6EEDAB60F191})
------------------------------
The connection type "EXCEL" specified for connection manager "{E3861233-443A-439A-BB8D-2777D84DB343}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
({C3728B0D-B172-4246-9B14-6EEDAB60F191})
------------------------------
BUTTONS:
OK
------------------------------
Data for Source Column 15 'Notes' is too large for the specified buffer size. How do I get around this, I can see some of the notes entries are beyond 255 chars so I changed the destination datatype to textI have never seen this error when importing before. What do I do?
View 1 Replies View RelatedHi every body.
As you can see in the title I would like to import my tables from the data base which is in the SQL Server, to an mdf file which can be attached to the project.Can some one give an indice please?
hi all,
While importing into sql server 2000 from a text file i am getting an error message like not enough disk space available to perform this operation but there is enough space (around 18gb). Please advice why this is happenning as my work is stuck
thanks and regards
jk