I am trying to copy the data in excel file into a table using the bcp
and this is the code that I have. However the bcp utility does not
seem to create a format file, which I thought it should do. I am
probably going about this all wrong so any help would be useful.
exec master..xp_cmdshell '(FOR %i IN ("E:WUTemp*") DO (bcp
#ProspectImportTest in "%i" -fE:WUTempProspect.fmt)'
bulk insert #ProspectImportTest from 'E:WUTemp*."' with (formatfile =
'E:WUTempProspect.fmt')
I am stuck at one place, where I have to convert CSV format file data into SAP IDOC format file. In SSIS we don't have any such SAP adapter (though we have .NET Data Provider for mySAP suite [SSIS SAP Adapter] but this is still not fully supported by Microsoft, plus it doesn't have feature to convert data into IDOC format) that can do this. Can someone here please provide me some pointers on any third party adapters available in market to do this job or if anyone has already developed some custom approach to achieve this task?
Your quick response on this is highly appreciated.
Similar to a previous post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=244646&SiteID=1), I am trying to import data into a SQL Table.
I am trying to program a small application that will import product data obtained through suppliers via CD-ROM. One supplier in particular uses Fixed width colums, and data looks like this:
Example of Data
0124015Apple Crate 32.12
0124016Bananna Box 12.56
0124017Mango Carton 15.98
0124018Seedless Watermelon 42.98 My Table would then have: ProductID as int Name as text Cost as money
How would I go about extracting the data with an XML Format file? I am stumbling over how to tell it where to start picking up data for a specific column. Is there any way that I could trim the Name column (i.e.: "Mango Carton " --> "Mango Carton")?
I don't know if it makes any difference, but I've been calling SQL from my code by doing this:
Code in C# Form
SqlConnection SqlConnection = new SqlConnection(global::SQLClients.Properties.Settings.Default.ClientPhonebookConnectionString); SqlCommand cmd = new SqlCommand();
SqlConnection.Open(); cmd.ExecuteNonQuery(); SqlConnection.Close(); RefreshData(); I am running Visual Studio C# Express 2005 and SQL Server Express 2005.
Hi,I am trying to use BULK INSERT with format file. All of our data hasfew bytes of header in the data file which I would like to skip beforedoing BULK INSERT.Is it possible to write format file to skip these few bytes ofheader before doing BULK INSERT? For example, I have a 1 GB data filewith 1000 byte header. Except for first 1000 bytes, rest of the data isgood for BULK INSERT.Thanks in advance. Sorry if it is really a dumb question as I am newto BULK INSERT and practicing still.Bob
Hi, I want to retrive the values from the database 'northwind' and then i want to store the backup files in "D:/Sample/north_database.bak" format(local machine). I retrive the database values in .txt,XML format. Now i want to take in .bak format. give the Suitable solution for this. Subashini.G
I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
My datafiles are bcp out from Syabse 11. Because the order of fields are different, I need to use format file with BCP.
I generated my fmt files by using MSSQL's bcp utility with the instructions from SQL Server books onLIne (section: Using a Format File to Selectively Copy data)
I cannot bcp in with format file. I got
Starting copy... DB-LIBRARY error: Unexpected EOF encountered in BCP data-file. DB-LIBRARY error: Unexpected EOF encountered in BCP data-file.
0 rows copied. Network packet size (bytes): 4096 Clock Time (ms.): total = 50
Since the datafile is small, I manually shift the order. Then, I bcp it in without format file and it worked fine. Now I got data in my SQL Server
I thought it may be the datafile. Though it's text file, it's generated by Sybase bcp utility. :-) So, I bcp out the data to a new ASCII file. I change the ID to make those records different. Then, I re-generate the format file. Now, I have everything generated by MSSQL. I tried again and I got the same error.
This table contains 1 Blob field and 4 varchar(255) fields. Will this be a problem?
We have to solve this problem. When our applications go live, the datafiles will be huge and we cannot edit it manually.
What are format files and how do they help us in Bulk Load???? I referred to many issues send to the forum but could not get sufficient information on this.
My main issue is to bulk insert from a .CSV file in hte following format
After bulk insert I am getting the double qoutes also into the table. Now my problem is, How do I remove these double qoutes? I don't want to use replace() to remove these quotes!!!!!! I have attached a Screen Shot to describe this issue with this mail
Any information is appretiated regarding this issue.
Hi.I was wondering if anybody could piont me to a reference for the .bakfile format.I need to upload a huge amount of data from a unix machine into an MSSQLServer database and have tried lots of different things with mixedresults.I occurs to me that if I can generate a .bak file on the unix side,move it over to the MS side and 'restore' the database, that wouldprobably be the fastest possible method.Thanks,-jim
A format file provides a way to bulk copy data selectively from a datafile to an instance of SQL Server. This allows the transfer of data toa table when there is a mismatch between fields in the data file andcolumns in the table.I take it this assumes the number of fields in the data file willalways be constant. What if it is not?My table has two columns but my datafile may have 2 to 4 columns and Iwant to always select only the first two. Is there a way to set up theformat file to accomplish that?
I have AdventureWorks installed. I am using SQL Server 2005.
I need to import some large tab delimited text files into SQL. From the research i did I thought that using BCO would be the best solution becausr I will have to import these files and export in the same format.
I am attempting to follow the example at: Creating a Format File
http://msdn2.microsoft.com/en-us/library/ms191516.aspx B. Creating a non-XML format file for character data
In my command window at C: I enter: bcp AdventureWorks.HumanResources.Department format nul -c -f Department-c.fmt -T I was expecting to be prompted with questions about the source file and have a format file created. When i press enter nothing hallende. No errors. Nothing.
Can come one give a beginner the step by step on how to creat the format file and use it to import (and export would be nice too).
I want to create a txt file from a table i have. I have the data in the correct formats but i want to include some padding around my selected four columns.
I have a format file which is working but not correctly. It is, forsome reason, dropping the first line of the input .csv file. Theproblem is something with the second coulumn of data having quotes init. Any ideas? Below is some info.Format file (I use firstrow=2 in Bulk Import command:8.061 SQLCHAR 0 3000 ","" 1 Provider_Raw_ID Latin1_General_CI_AS2 SQLCHAR 0 3000 ""," 0 none_name Latin1_General_CI_AS3 SQLCHAR 0 3000 "," 0 none_Spec_orig Latin1_General_CI_AS4 SQLCHAR 0 3000 "," 3 SpecialtyCode Latin1_General_CI_AS5 SQLCHAR 0 3000 "," 2 Category Latin1_General_CI_AS6 SQLCHAR 0 3000 " " 4 NetworkComparedTo Latin1_General_CI_ASSample input file:ID,NAME,SPEC_ORIGINAL,SPEC,CATEGORY,NetworkCompare dTo1,"Aaron, Arnold H, DO",Family Practice General Practice,FP,PCP,netcomp12,"Aaron, Arnold H, DO",Family Practice General Practice,FP,PCP,netcomp13,"Aaron, Arnold H, DO",General Practice,GP,PCP,netcomp14,"Abae, Mick, MD",Reproductive Endocrinology,OBEN,OB,netcomp15,"Abanilla, Fernando M, MD",Nephrology,IMNE,SPEC,netcomp16,"Abaunza, Ramiro J, MD",Obstetrics/Gynecology,OBGY,OB,netcomp17,"Abaunza-Fiallos, Yanina J, MD",Pediatrics,PD,PED,netcomp18,"Abbas, Rahat, MD",Internal Medicine,IM,PCP,netcomp1Thanks a lot!!!Andrew*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
I have a table that I want to export to a flat file. The problem I am running into is, the person I am sending it to needs it in a specific format. It is a comma serperated file and I need quotes on some of the data but not all.
For example : "1234",abc,"id"
I know how to make it all or none but not conditionally. Also I have some fields that the total in them is 0.00 and when this gets exported to the file the format become .00, is there a way to make it 0 with out changing the ones that have totals. Thanks.
The data file contains the answers given to questions asked during the course of an interview. This file contains data only for completed interviews. The file is ASCII based, and contains data in a card column format. This means that each record is spread over several rows in the file €“ called cards. Currently 39 cards make up one completed record, although this may change over time. There are 80 columns per row.
I have a flat file which the first row contains the certain info. about this file. I want to read the first line from this file to determine if continue to next step. what's the task/transformation can be used to do this?
Hi, How can I convert a text file (.txt) into SQL in ASP.net 2.0 ? The sample of the file format is like that ... 09/03/2007 08:41 "Fung, Kitty" Granted Access D1 Main 2354 111 09/03/2007 08:42 "Ng, Jaclyn" Granted Access D1 Main 21906 18 09/03/2007 08:42 "Leung, Agnes" Granted Access D1 Main 21920 18 Cheers
I want to use the bulk insert statement to insert data from a text file that contains more columns than the target sql table does. I am using SQL 7.0.
I am using a format file, but I can't work out how to achieve the above. SQL books online (and the msdn website) do not describe how to do this, but it is intimated that it can be done.
I added a field to an exisiting table (CHAR 30), and I added to field to the BCP Format File. The BCP worked fine before the new field, and still works fine when I exclude the new field from the format file, but with the new field I receive the following error:
SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP format file
Has anyone had experience formatting an excel file (i.e. run a macro) after it is (created &) outputted from a DTS package?
Also an easier question: What is the best (easiest) way to create a unique filename in Excel with a datetimestamp in the file name (i.e. MyFile-20040608.xls)
Hi, i wanted to ask on the same context of bcp...i m trying to insert two rows into a table using the bcp command prompt and the bcp file looks as follow(format.fmt): 7.0 3 1 SQLCHAR 0 4 ""," 1 numbers 2 SQLCHAR 0 15 """ 2 values 3 SQLCHAR 0 2 " " 3 finish
and the input file has the following contents 1234,"other" 5678,"column"
now when i m running the command as bcp master.dbo.two_column in c: empinput.txt -fc: empformat.fmt -Sserver -Uuser -Ppasword -T
it gives me the error as sqlstate 07009 with native state =0 and the error as = [Microsoft][SQL Native Client] Invalid descriptor index
i m using sql server 2005 (YUKON)...can anyone pleaseeeeeeeeeeeeee tell me how can i solve this problem?????????????????so that i can insert these rows into my table two_column.... thanks ...pleaseeeeeeeeee help me....
Howdy all. I havent used format files inside BCP in several years and am having trouble creating one now.
declare @exec varchar(1026) set @exec = 'bcp faa_ivr.dbo.primary_informant format -SboxNameinstanceName -c -T -f\destinationFAAIVRprimary_informant_format.txt ' exec master..xp_cmdshell @exec
output --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQLState = 08001, NativeError = 17 Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied. SQLState = 01000, NativeError = 2 Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()). NULL
(5 row(s) affected)
I've tried brackets ([])around the box/ instance name. I've tried using the FQDN. I tried the SA account instead of WINNT authentication. All ideas are appreciated.
Can someone please point me as to where I can find info for the following. I have seen this somewhere but I am unable to find it.
I am trying to do INSERT INTO Table SELECT ... FROM OPENROWSET(BULK '\sharedDriveDataFile.txt', FORMATFILE = '\sharedDriveFormatFile.txt', FIRSTROW = 2) AS Q
How can I make that shared drive dynamic? as such..hoping not to use dynaic SQL INSERT INTO Table SELECT ... FROM OPENROWSET(BULK @SharedDrive DataFile.txt', FORMATFILE = @SharedDrive FormatFile.txt', FIRSTROW = 2) AS Q
Hi All! I am using Bulk Insert to obtain data from the text file. This works perfectly if i try to import it using standard/default Bulk Import . However, if i try to import using Format file by specifying the two columns (as data is in two column format) , the import works successfully but applying a select statement only returns garbage in the rows. What is exactly happening, i could not able to understand.
If I make a XML formatfile which look like this: <?xml version="1.0"?> <BCPFORMAT ...> <RECORD> <FIELD ID="1" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="20"/> </RECORD> <ROW> <COLUMN SOURCE="1" NAME="ABLBELNR" xsi:type="SQLNVARCHAR"/> </ROW> </BCPFORMAT> then the import failed with the following error message: [Microsoft][SQL Native Client]All bound columns are not read-only
If I delete the identity column then the import works correct.
What can I do, so that I can import with the identity column ?
HiI want read a trace file generated by SQL Server 2005 througr SQLServer 2000.But fn_trace_gettable function in SQL 2000 does not recognize the fileas of proper format.If there is some other tool or utility available through which i canread the file generated by SQL Server 2005.Or if I can get the file format of the file then I will write my owntool.ThanksPushkar
The IT shop I work in installed MS-SQL Server last week for the solepurpose of converting a large MS-SQL Server database to a format thatInformix (our main database engine) can read. MSSQL Server installedperfectly, I can view/manipulate the little "Northwind" sampledatabase, back it up to a file, restore it (Microsoft Tape Format) justfine. But... The MSSQL Server "backup" file they sent me that I needto restore is in a format that I'm unfamiliar with. It is a large*.sqb file and when I try to restore (using Enterprise Manager - Rightclick on "empty" database, choose All Tasks, Restore Database - FromDevice, Point to File - I get the message: File is not a validMicrosoft Tape Format backup set.Looking at my little Northwind backup file in a hex editor I see thecharacters "TAPE" as the first 4 characters of the file. Looking atthe file that I'm unsuccessfully trying to restore in a hex editor Isee "SSBE". It's not in the standard "Microsoft Tape Format"apparently, but does anyone have any clue what might have created thisbackup file? I hope it's not some weird proprietary program! Anyhelp appreciated, thanks...Joe
