MS SQL Server :: BCP Format File

Adapter To Convert CSV Format File To SAP IDOC Format

Nov 16, 2006

Hi All,

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.

Regards,

Kuldeep Chauhan

View 2 Replies View Related

XML Format File For Bulk Insert Of Text File With Fixed Length Columns

Jan 2, 2008

Hey All,

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();

cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO PhonebookTable(Name, PhoneNumber) VALUES('" + txtName.Text.ToString() + "', '" + txtPhoneNumber.Text.ToString() + "')";
cmd.Connection = SqlConnection;

SqlConnection.Open();
cmd.ExecuteNonQuery();
SqlConnection.Close();
RefreshData();
I am running Visual Studio C# Express 2005 and SQL Server Express 2005.

Thanks for your time,

Hayden.

View 1 Replies View Related

Is It Possible To Write A Format File That Skips A Few Bytes Of Header In Data File?

Jul 23, 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

View 7 Replies View Related

Analyse File Format By File Flat Connection

Jan 16, 2008

I have a txt file with format as following

MailAddress:Kienpt@ifi.local
DomainName:ETH2K

[Date]2007/12/27 15:02:50 [Operation]
[Date]2007/12/27 15:02:50 [Operation]
[Date]2007/12/27 15:02:50 [Operation]

I want to use File Flat Connection to analyse format of this file. And i want each record after analysing include 4 fields as folowing:

- MailAddress, DomainName, Date, Operation
(Mail Address and DomainName is same in each record)

Can you help me?

View 1 Replies View Related

How To Get The Database File Into .bak File Format Using C#

Feb 20, 2007

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

View 4 Replies View Related

SQL 2012 :: Creating Dynamic SSIS File Format - Dynamic CSV File As Output

Mar 2, 2014

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.

View 3 Replies View Related

Cannot Bcp With Format File

Mar 10, 1999

Backend: MSSQL 65

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.

Please advise
Thanks in advance.

Elizabeth Pickering

View 1 Replies View Related

Getting File Name In MM Format

Feb 15, 2005

set @date = getdate()
set @month = convert (varchar[2], datepart(mm,@date))

gives me 2 instead of 02 for month of February

any clues ?

View 3 Replies View Related

Format File

Jan 27, 2004

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

12,23,"blrnnr","Address",45677,"bangalore".............68 columns

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.

Thanking you
Naveen

View 1 Replies View Related

Bcp With Format File

Mar 8, 2006

I am trying to copy the data in excel file into a table using the bcpand this is the code that I have. However the bcp utility does notseem to create a format file, which I thought it should do. I amprobably 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')Thanks.KR

View 2 Replies View Related

.bak File Format?

Oct 15, 2007

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

View 2 Replies View Related

Description Of *.ldf File Format

Apr 28, 2002

do anyone know the format of the *.ldf file?

View 2 Replies View Related

Creating A BCO Format File

Oct 3, 2007

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).

View 2 Replies View Related

Output Txt File In Certain Format

Oct 25, 2007

Using sql server

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.

Im not sure about the best way to go about this?

View 1 Replies View Related

Format File Easy One (not For Me)!

Jul 20, 2005

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!

View 5 Replies View Related

Flat File Format

Dec 20, 2006

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.

View 4 Replies View Related

File Format Is Not Valid

Jun 12, 2015

when installing sql server error showing file format is not vaild

View 3 Replies View Related

Convert Dat Format File

Apr 23, 2008

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.

sample of data is as follows

record 1

0000301 108033135 ds880783 100325080327000451334779005133477900 1 001
000030251334779000 0 LISA E 002
0000303MCCARTHY est 11000000000000000000000000100 003
0000304DODGE GRCARA 2008 004
0000305Northgate Chrysler Dodge Jeep 005
00003068536 Colerain Ave Cincinnati 45251006
00003072914OHDEMOSS,MIKE 03-26-200811 007
00003081 1 120529101010 9 008
0000309 009
0000310 010
0000311 011
0000312 2 012
0000313 013
0000314 014
0000315120642 1 015
0000316 016
0000317 017
0000318 018
0000319 019
0000320 020
0000321 021
0000322 022
0000323 023
0000324 024
0000325 025
0000326 026
0000327 027
0000328 028
0000329 029
0000330 030
0000331 031
0000332 032
0000333 033
0000334 034
0000335 035
0000336 120557 036
00003373108629008110.000 North-gate Chrysler Dodge Jeep 037
0000338 1 3 038
0000339 039

Record 2

0000401 108033135 ds880609 300192080327000931868842133184587707 1 001
000040231868842131 0 MARK 002
0000403MURPHY cst 10010000000000000000000000100 003
0000404DODGE 2005 004
0000405Hebert's Town and Country Dodge Chrysle 005
00004061155 E Bert Kouns Shreveport 71105006
00004075628LA436 03-25-200811 007
00004083 1 120608 008
0000409 090909 009
0000410 4 010
0000411 011
0000412 2 012
0000413 013
0000414 014
0000415120759 1 015
0000416 016
0000417 017
0000418 018
0000419 019
0000420 020
0000421 021
0000422 022
0000423 023
0000424 024
0000425 025
0000426 026
0000427 027
0000428 028
0000429 029
0000430 030
0000431 031
0000432 032
0000433 033
0000434 034
0000435 035
0000436 120642 036
000043731086290081 09.000A-Bears Town and Country Chrysler Jeep 037
0000438 1 3 038
0000439 039

i need to convert this into a coma delimtited file ,file and i am failing whats need to be done to achieve it

its quite a new concept for me

please advise

View 4 Replies View Related

File Format Validation

Jul 22, 2007

Hi all,

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?

thanks

Patrick

View 1 Replies View Related

Convert Text File Format

Mar 30, 2007

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

View 2 Replies View Related

Bulk Insert Using Format File.......

Apr 8, 2001

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.

Any suggestions ?

Regards,

Stuart.

View 1 Replies View Related

I/O Error Reading BCP Format File

Jul 20, 2000

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

(2 row(s) affected)

Here is a copy of my format file:
7.0
11
1 SQLCHAR 0 6 "" 1 Empl_ser_no
2 SQLCHAR 0 2 "" 2 Empl_stat_cd
3 SQLCHAR 0 1 "" 3 Guest_type_fl
4 SQLCHAR 0 30 "" 4 Job_tl
5 SQLCHAR 0 14 "" 5 Pager_no
6 SQLCHAR 0 1 "" 6 Security_Clear_fl
7 SQLCHAR 0 9 "" 7 SSN
8 SQLCHAR 0 30 "" 8 Emer_cont_nm
9 SQLCHAR 0 14 "" 9 Emer_cont_no
10 SQLCHAR 0 1 "" 10 Citizen_fl
11 SQLCHAR 0 30 "
" 11 Job_Res

The input file is just a fixed positonal text file, and I have checkid it 100 times- it is fine.

Any sugestions from any BCP gurus out there? THANKS

View 1 Replies View Related

DTS Output Excel File - How To Format?

Jun 8, 2004

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)

Thanks!

View 3 Replies View Related

Bcp File Format...its Killing Me...pleaseeeeeeeee Help...

Sep 20, 2005

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....

View 5 Replies View Related

Problems Creating Format File.

Jul 6, 2006

sql2k sp4

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.

View 4 Replies View Related

SQL 2012 :: How To Create XML Format File

May 21, 2015

I see this all over for creating a Format file:

bcp Northwind.dbo.Products format nul -c -f "c:Products.fmt" -T

Or this for XML

bcp AdventureWorks.HumanResources.Department format nul -c -x -f department.xml -T -S servername

I get an error saying "cannot open a connection to SQL SERVER"

The SQL Server is local on my laptop.

Here is the exact code I am using:

bcp OrderDb.CompanyDepartment foramt nul -c -x -f c:CompanyDepartment.fmt -t, -T

And I have tried :

bcp OrderDb.CompanyDepartment foramt nul -c -x -f c:CompanyDepartment.xml -t, -T

Also, some say to put the server name at the end, but then I get an unknown argument on command line error

View 1 Replies View Related

OPENROWSET Format File Dynamic

May 22, 2008

Hi,

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

View 1 Replies View Related

Exporting File Name In Specific Format

Jul 17, 2013

I tried to scheduled and save a Report Via SSRS. The Exporting file name need to be in this format -- 17July2013_NewSystem.csv

I have tried using @timestamp in file name, but the file was saved like this 2013_07_17_090529_NewSystem

How to specify the filename to get the desired format ie. the file name deposited in the folder be --

17July2013_NewSystem.csv
18July2013_NewSystem.csv (for tomorrow and so on)

View 1 Replies View Related

Rubbish Appear When Impored Using Format File

Nov 26, 2007

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.

View 2 Replies View Related

Import Failed With BCP And XML-Format-File

Jan 21, 2008

I have a table with 2 row:

CREATE TABLE [dbo].[EABL](
[DSID] [int] IDENTITY(1,1) NOT NULL,
[ABLBELNR] [nvarchar](255) NULL)

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 ?

Thanks for you help
Fasse

View 6 Replies View Related

Profiler Trace File Format

Jul 23, 2005

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

View 1 Replies View Related

SQL Server Backup File Format

Apr 18, 2006

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

View 1 Replies View Related