I've tried to import a text file into a table using the Import/export wizard.
My problem starts with some columns that should have NULL values (i.e. zero-length string) but the wizard doesn't recognize that it's NULL.
How do I solve the problem?
Thanks,
Mich
I can't figure this one out...
I have an Excel spreadsheet (Excel 2003 format) with a column of values. The column is formated to General (although I've tried it formated to text). The values are alphanumeric. However, there are a few values that are completely numeric.
Example:
_ITEMNUMBER_
1-528
K214-5
184PR
45678
As can be seen, the last value is completely numeric. I am importing into a table with one column that is formated as nvarchar(50).
I use dtswizard.exe and do a simple import into the table and the alphanumeric values import just fine but anytime a value is completely numeric it has a NULL value in the table instead of the value that should be there.
I've tried changing the format of the column to nchar, char, etc, etc. Always comes in NULL.
Any ideas?
Hi all,
Hopefully I am posting this question in the correct forum. I am still learning about SQL 2005. Here is my issue. I have an access db that I archive weekly into and SQL server table. I have used the dst wizard to create an import job and initally that worked fine. field I have as the primary key in the access db cannot be the primary key in the sql table since I archive weekly and that primary key field will be imported several time over. I overcame this initally by not having a primary key in the sql table. This table is strictly for reference. However, now I need to setup a unique field for each of the records in the sql table. What I have done so far is create a recordID field in the sql table that is an int and set as yes to Identify (auotnumber). That worked great and created unique id for all existing records. The problem now is on the import. When I try to import the access table i am getting an error because of the extra field in the sql table, and the error is saying cannot import null value into this field. So... my final question is how can I import the access table into the sql table with one extra field which is the autonumber unique field? Thanks a bunch for any asistance.
Bill
Hello all.
I was wondering if there was a simple Import statement I could use in SQL to import an Access Table into SQL Server 2005.
I know how to use the SSIS Import/Export Wizard, but that seems excessive to import a single 204 record table
Any help on this would be greatly appreciated.
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"
Hai Friends,
Actually i want to import excel sheet in to sql server tables. I am writing the following query SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:Book1.xls','SELECT * FROM [Sheet1$]') and able to retireve the data but the problem is the data present in the excel sheet is
Username
password
hello
12345
12345
hello
hai-123
haihai
haihello
hello-123
Once i run this query i am getting it as
Username Password hello NULLNULL hellohai-123 haihaihaihello hello-123can anybody please help me out in this case and how to solve this problem and if you have any piece of code please pass it on to me and i am in very urgent need waiting for your replies thanks in advance.
Hello,
I am using the SSIS import/export wizard to import an Access table into a sql server 2005 database. The table has fields in Arabic (name, last name, etc.) and non-Arabic fields (gender, phone number, category, etc.).
The destination table has nvarchar columns.
After the import, I can see the Arabic characters in the destination table, but they appear in inverse order (from left to right). In Access (or Excel), Arabic fields appear as they should (from right to left) and non-Arabic fields are OK as well (from left to right).
If I do a simple copy-and-paste of a "correct" Arabic text into the table, the result is still wrong (inversed letters)...
Please help, I can't see what else to do.
Thank you.
Hi all,
I need advice on how to get data from xml file into the preexisting table. Right now I have a process that scrapes data from the website and generates xml file everyday. I need to get this data into sql table every night and I would like to make this automated process. What do I need to use in sql to accomplish this? It would be great if you can point me to a tutorial or give me few pointers.
Thanks!
I 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
Hi guys,
I wanted to know if there was a way to import multiple sheets from one excel file into one sql table in one go.
I have an excel file which has 15 sheets and want to import them into one table in one process rather than having to create 15 table and then joining into one table.
I am using Sql Server 2005(enterprise edition) v9 SP2.
Thanks
Krunal
I'm using an ObjectDataSource in Visual Studio to retrieve records from a SQL Server 2005 database.
I have a very simple dilemma. In a table I have fields FirstName, Surname, Address1, Address2, Address3 etc. None of these are mandatory fields.
It is quite common for the user not to enter data in Address2, Address3, so the values are <null> in the SQL table.
In Visual Studio 2005 I have an aspx form where users can pass search parameters to the ObjectDataSource and the results are returned according to the passed in parameters.
The WHERE clause in my Table Adapter is:WHERE (Address1 LIKE @Address1 + '%') AND (Address2 LIKE @Address2 + '%') AND (Address3 LIKE @Address3 + '%') AND (FirstName LIKE @FirstName + '%') AND (Surname LIKE @Surname + '%')
If, for example, I simply want to search WHERE FirstName LIKE ‘R’, this does not return any results if the value of Address3 is <null>
My query is this: Could someone please show me the best way in Visual Studio 2005 to return records even if one of the Address fields is <null>.
For reference, I have tried: Address3 LIKE @Address3 + '%' OR IS NULLThis does work, however itsimply returns every instance where Address3 is <null> (accounting for about 95% of the records in the database). Thanks in advance Simon
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 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 RelatedI am importing csv files into sql 2000 using odbcbcp and am getting the '' symbols when there are null values. Has anyone ever seen this? Or know how I can prevent this from happening? I am required to use odbcbcp to import this data.
View 5 Replies View RelatedI'm using SQL Server Express and am trying to import a CVS file. The CVS file contains a string field (named DAS) that represents a Date. This field can be null.
I've tried using the DTS Wizard to import this CVS file and convert the DAS field to a Date, which works great until it hits a record with a NULL DAS field. It then throws a convertion error.
Still using the DTS Wizard, I've changed the DataType of the DAS field in the Source file to [DT_DATE], it works fine but all the null dates are converted to 12/30/1899.
Is there a way (DTS Wizard or something else) that will allow me to import these CVS files with null Date fields and keep them as null in SQL Server table.
Thanks for any help,
Jon
I have about 50 Excel files from which I have to import data with some transformations to Sql Server. My first approach was to use Excel Source component in a data flow to read the data . However, as it turned out column X in some files was being converted to a DT_NTEXT blob and in other files it was mapped to WSTR. The reason I guess is because column X contained string of varying sizes - some greater than 255 characters while others less than 255 ( max was 3000 ) . My package used a ForEach loop to iterate over all the Excel files in a directory and feed that to the data flow task. I played around with IMEX and TypeGuessRows setting but they didnt help me . In my second approach I used 2 Excel sources ; one set up for the blob type and the other for the string type . I joined them together using a precedence contraint. This worked but I then figured out that there were 2 other columns in my data that exhibited the same behavior. I couldnt continue with the mulitple Excel source approach cause I would then have 8 Excel source components. Finally, I played around with Execute SQL Task . I selected the columns X,Y and Z , initialized 3 variables of type Object , used a ForEach to enumerate over the dataset and feed that to a script component that converted the objects to Strings. This seems to work for all types of data in the mulitple columns.
My question - has anyone encountered such problem ? What was the solution ? Just thought I would share this with the rest of the community. I cant seem to recall what the exact error I was getting ..but it was something like "cant convert long data to string " or something . I also keep getting annoying error icons in my Excel Source components used in the foreach loop. Something to do with acquire connection failed even after I set DelayValidation to "true".
Thanks
I have a xml file downloaded from webAPI which i want to import into my database.There are several sub childnodes and need to combine data between nodes.
<?xml version="1.0" encoding="UTF-8"?>
<classes type="array">
<class>
<class_id>3S</class_id>
<class_pk type="integer">3900</class_pk>
[code]....
Here is the scenario that I cannot resolve
CREATE TABLE [dbo].[tEvents](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[EventName] [varchar](1000) NOT NULL,
CONSTRAINT [PK_tEvent] PRIMARY KEY CLUSTERED
(
[EventID] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[tEventSelections](
[EventSelectionID] [int] IDENTITY(1,1) NOT NULL,
[EventID] [int] NOT NULL,
[StatusPID] [int] NOT NULL,
CONSTRAINT [PK_tEventSelections] PRIMARY KEY CLUSTERED
(
[EventSelectionID] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
then try this
SELECT e.eventName, es.statuspid
INTO #tmpTable
FROM tEventSelections ES
INNER JOIN tEvents E
ON E.EVentID = ES.EventID
INSERT INTO #tmpTable (eventName) values ('Another One')
DROP TABLE #tmpTable
this causes a null insert issue
(0 row(s) affected)
Msg 515, Level 16, State 2, Line 7
Cannot insert the value NULL into column 'statuspid', table 'tempdb.dbo.#tmpTable___________________________________________________________________________________________________________000000000130'; column does not allow nulls. INSERT fails.
The statement has been terminated.
So how do I allow the null, as the not null is coming from the ES table. But I want to allow the insert to happen without having to create the table first, this code works in SQL 2000 but fails in 2005, inserting all fileds into the insert also has it's own issues as some of the fields are delibertly left blank so in some circumstances the data returned to a grid displays correctly.
This method has been used in quite a lot of stored procedures and will be a nightmare to correct if each has to be edited.
One example of the use of is to return a dataset and then add a row at the bottom which is a sum of all the rows.
Regards
I know that the DTS Wizard is supposed to be able to handle heterognous
data imports but I can't get it to work with a free FoxPro table. I have to export to a text file and then import from the text file and spend an hour renaming columns and farting around with datatypes.
CAN I GET A FOXPRO TABLE INTO SQL SERVER DIRECTLY OR NOT?
HOW DO I DO IT?
I don't fully understand some of the questions the DTS Wizard is asking. Can anybody give me a blow by blow account before I ring the Samaritans?
Thank you and Happy Christmas - it may be my last if I can't speed up these imports. Either I'll jump under a bus or my boss will make mince pies out of me.
Thanks
Mark
I am update/inserting records threw a web form in vb.net. I need to insert 'NULL' into my microsoft sql server database. I am not talking about the below line of code where website is the name of my paramater. If i do that it will just place a blank into that field in the database. If i dont enter anything into that textbox I want it to to say NULL in that field. So if I go into the actual table in the SQL Server Management Studio and look at the website field of the recored I just added or updated and did not type anything into the web site textbox it needs to say NULL. I also tried the second line of code but that places a single quote in front and behind NULL. So the field will have the value 'NULL'.
website.value = txtwebsite.text.tostirng
if txtwebsite.text ="" then
website.value ="NULL"
end if
The reason why I need the NULL there is because I bind the website filed to a hyperlink template in a gridview. The actual text of the hyperlink is bound to PAYER which is a name of a insurance payer but the navigateto is bound to the website field. If i do not enter any data into the website field and it stays as NULL, then when my gridview is loaded, payers that dont have a website will not be underlined and user wont have the option to click on them since there is no value for the navigateto. But if i use my form to update/add a payer and leave the website textbox blank in puts a blank into that field in my database and when it loads that new row into the gridview the PAYER is underlined and u can click on it but it will just take u to the web site is unavailable page. So is there anyway to actaull have NULL placed into a filed and not just blank space
I need to import csv data into a SQL Server 2005 database using SQL Server Management Studio Express. I can't find any menu options for accomplishing this. Is this a limitation of the Express edition, or am I missing something when I try to find this feature?Thanks for any help provided.
View 7 Replies View RelatedDear friends i want to import excel file into sql server 2005 in asp .net using c# can anyone help me?
rgds,RK
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?
View 2 Replies View RelatedHi 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
HiI've got an Ingres database of some 200 tables which I need to importevery night into SQL Server 2005 for use by Reporting Services. Mostof the tables will come across unchanged (a few need massaging tohandle time intervals correctly), but the Import Wizard only seems towant to import one table (or more accurately query) at a time. I seemto remember the old 2000 Import Wizard handled multiple tables - isthere any way of processing multiple tables in 2005, or must I resignmyself to writing 200 import packages in SSIS.Chloe CrowderThe British Library
View 4 Replies View RelatedI'm trying to import a database. It seems my most likely route is via Excel, so I've moved my tables in there.
Then I tried setting up Excel as a linked server as described in http://support.microsoft.com/default.aspx?scid=kb;en-us;Q321686
My linked server gets created apparently with no errors, but I can't see any contents when I click on it in the object explorer.
Anyone with more clues than me?
thanks
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
)
also i was wondering, that i have been reading that with this sp2 for sql server 2005 express you are able to use the dts wizard, is this true? i have a few databases in access that i would like to import into sql, would i have to create the tables first in sql then import or will the import also bring the tables & structure in?
thank you to all
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
Hi,
I am using Windows 2003 server and Sqlserver 2005 by the use of Linked server , I made a connection to Oracle 10g after that I am importing records from Oracle to sqlserver 2005. When I made tnsnames.ora in sql machine , it worked fine but when i am using tnsnames file from oracle server then i fiired importing procedure it returns below maintain error :
OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Unspecified error".
OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS". The provider supports the interface, but returns a failure code when it is used.
Please let me know.
Thanks
Hi,I have to find a way to do the following : a company provides for my company a web page displaying data separated with ;I need to automatically update a table in SQLServer with this data, ie delete the old data, read the web page containing new data as text, and insert these data into the table.I would actually prefer not to trigger this import process "by hand" ; if there's a solution to schedule it ...Thanks for your helpJohann
View 3 Replies View RelatedHello
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 have installed SQL Server Managemert Server Express .... I am wondering how to import data from an excel spreadsheet?
.. Silent Running