f



COMMA delimited TEXT FILE

Hi,

On SQLServer 2000, I have a table with a following structure:

MYTABLE
col1  char,
col2  date,
col3  number

My Objective:
------------
Externally (from a command line), to select all columns and write the
output into a file delimited by a comma.

My method:
---------
1. Probably will use OSQL or BCP to do this.
2. Use the following syntax:
select RTRIM(col1) +','+ RTRIM(col2) +','+ RTRIM(col3) 
from MYTABLE;

My 3 Problems:
-------------
1) If there is a NULL column, the result of concatenating any value with
NULL, is NULL.  How can I work around this?  I still want to record this
column as null.  Something like say from the example above, if col2 is
null, would result to:   APPLE,,5

2) The time format when querying the database is: 2003-06-24 15:10:20.
However, on the file, the data becomes: 24 JUN 2003 3:10PM.  How can I
preserve the YYYY-MM-DD HH:MM:SS format?  Notice that I also lost the
SS.

3) Which utility is better? BCP or OSQL?  
For OSQL, it has a "-s" flag which gives me the option of putting a
column separator. But the result is:
"APPLE     ,14 JUN 2003      ,        5"   
I don't need the extra space. 
While for BCP, there is no column separator flag.

You will notice from my inquiry above that my background in SQLServer is
not very good.

Thanks in Advance!!

Regards
Ricky

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
0
spricks (1)
6/25/2003 2:57:53 AM
comp.databases.ms-sqlserver 14567 articles. 1 followers. janinepelletier (108) is leader. Post Follow

1 Replies
405 Views

Similar Articles

[PageSpeed] 22

Hi ,

Suggested solution to your problems.

1) If there is a NULL column, the result of concatenating any value
with
> NULL, is NULL.  How can I work around this?  I still want to record this
> column as null.  Something like say from the example above, if col2 is
> null, would result to:   APPLE,,5

Solution>> Use ISNULL T-SQL Function. In your case it will be 
Select Filed1,Isnull(Field2,''), Field3 From TableName. This Isnull
function will replace value of field if null to supplied argument that
is ''.


> 2) The time format when querying the database is: 2003-06-24 15:10:20.
> However, on the file, the data becomes: 24 JUN 2003 3:10PM.  How can I
> preserve the YYYY-MM-DD HH:MM:SS format?  Notice that I also lost the
> SS.
Solution>> For 2nd porblem, change date time field value to varchar.
Thus query will be Select Filed1,Isnull(Field2,''), Cast (
DateTimeField3 as Varchar) From TableName.


Hope above hints help you.

Thanks, Amit


    Ricky Cruz <spricks@bigfoot.com> wrote in message news:<3ef90fb1$0$198$75868355@news.frii.net>...
> Hi,
> 
> On SQLServer 2000, I have a table with a following structure:
> 
> MYTABLE
> col1  char,
> col2  date,
> col3  number
> 
> My Objective:
> ------------
> Externally (from a command line), to select all columns and write the
> output into a file delimited by a comma.
> 
> My method:
> ---------
> 1. Probably will use OSQL or BCP to do this.
> 2. Use the following syntax:
> select RTRIM(col1) +','+ RTRIM(col2) +','+ RTRIM(col3) 
> from MYTABLE;
> 
> My 3 Problems:
> -------------
> 1) If there is a NULL column, the result of concatenating any value with
> NULL, is NULL.  How can I work around this?  I still want to record this
> column as null.  Something like say from the example above, if col2 is
> null, would result to:   APPLE,,5
> 
> 2) The time format when querying the database is: 2003-06-24 15:10:20.
> However, on the file, the data becomes: 24 JUN 2003 3:10PM.  How can I
> preserve the YYYY-MM-DD HH:MM:SS format?  Notice that I also lost the
> SS.
> 
> 3) Which utility is better? BCP or OSQL?  
> For OSQL, it has a "-s" flag which gives me the option of putting a
> column separator. But the result is:
> "APPLE     ,14 JUN 2003      ,        5"   
> I don't need the extra space. 
> While for BCP, there is no column separator flag.
> 
> You will notice from my inquiry above that my background in SQLServer is
> not very good.
> 
> Thanks in Advance!!
> 
> Regards
> Ricky
> 
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
0
amitb (15)
6/25/2003 5:29:55 AM
Reply:

Similar Artilces:

How to import a tab delimited text file into MS Access database
Hi, I have the following task to be done in VC++ 6.0. I have a tab delimited text file that needs to be imported into a MS Access table. The table is created and the table structure is defined using DAO methods. So now I have to fill this table with the data in the text files. For a few rows in the text file, the number of columns could be less than the number of columns in the table. Can anyone please post me the code to do this. Thanks a ton in advance. Pradeep wrote: > I have the following task to be done in VC++ 6.0. We'd be happy to give you the VBA code, but do you really want to throw away your C++ and use Microsoft Access for the job? If you want to keep C++, you can ask for C++ code in another newsgroup, because we'd just be guessing. -- Message posted via http://www.accessmonster.com ...

comma delimited text file.
Hi. I have a comma delimited text file that I want to parse. I was going to use fscanf from the C library but as my app is written in C++ I thought I'd use the std io stream library... My Text file looks like: First_Name, Last_Name, ID, Date/Of/Birth<newline> depending on the host the newline is either \n or \r\n so what would I do to input these lines from a file? std::cin >> firstName >> comma >> lastName >> comma >> ID >> comma >> dateOfBirth; The above obviously won't work, but I'm looking for a statement similar to this to ...

Import MS Office Excel Comma Separated Values Files into a MS Access 2003 database
Hi, I have been tasked with Importing MS Office Excel Comma Separated Values Files into a MS Access 2003 database. These files were exported from a DB2 database.I have also been given the DDL code. I have Googled a solution to this task and have tried creating these tables using the DDL code in a query within a blank MS Access database. Is there a more expedient way to achieve this? I noticed, for example, that a field in DB2 has a data type of "decimal" while the same field would have the data type of "number". Thanks, Zuf What follows is the DDL: -------...

Output to comma delimited text file
I need to create a text file that has the data from the 10 tables in the database. The number of fields in the tables exceeds 255 and so I cannot make a new table with all the fields and then export it into a text file. Is there any s/w out there I could use? I am not much of a programmer but I heard I could use VBA to get this done. Any help with the code will be appreciated. Thanks your post is not completely clear, but I will give it a shot. I understand that you want to write data from 10 tables to 1 text file. What is not clear is if the tables all have 255 fields or not. The other issue is that data contained in each field. You want a comma delimited text file. If there is any punctuation in any of the fields, and that punctuation happens to be a comma, it will throw off your delimiter (the comma) because you will have 2 commas for that field. Lets say all the tables have 255 fields and there are no commas in any of the fields (actually, it doesn't really matter if the tables all have the same number of fields for writing to a text file - but reading from the text file to a table might make a difference). Then you can do this using VBA. The following code assumes you are writing data from an mdb file: Sub WriteDataToTextFile() Dim DB As DAO.Database, RS As DAO.Recordset Dim str1 As String, strPath As String, arrTbls As Variant Dim i As Integer, j As Integer strPath = CurrentProject.Path arrTbls = Array("tbl1", "tbl2", "tbl3",...

Comma delimited text file-columns are different
I have to read text files which are comma-delimited. I tried load and importdata but did not work because columns of line 2 are different from line 1. Could anyone give some hint? Thanks Data is like: BYRG_10_116N_25W_13_SCHMITZ_, 44.85469, 93.77724, 438587, 4967103, 294, 1975_08, 31, A, 1.14, , 0, , .13, , .12, , 0, , 0, , 0, , 0, , 0, , 0, , 0, , .01, , 0, , 0, , 0, , .20, , .10, , 0, , .35, , .11, , .06, , .10, , 0, , 0, , 0, , 0, , .42, , .27, , 0, , 0, , 0, , BYRG_10_116N_25W_13_SCHMITZ_, 44.85469, 93.77724, 438587, 4967103, 294, 1975_04, 31, A, ,M, ,M, ,M, ,M, ,M, ,M, ,M, ,M, ,M, ,M, ,M, ,M, ,M, ,M, ,M, .01, , ,M, ,M, 0, , .50, , 0, , 2.20, , 0, , .90, , .10, , .80, , .86, , .90, , .20, , 0, , "Eric " <tanzhao.eric@gmail.com> wrote in message <hepamv$h3d$1@fred.mathworks.com>... > I have to read text files which are comma-delimited. I tried load and importdata but did not work because columns of line 2 are different from line 1. Could anyone give some hint? Thanks > > Data is like: > > BYRG_10_116N_25W_13_SCHMITZ_, 44.85469, 93.77724, 438587, 4967103, 294, 1975_08, 31, A, 1.14, , 0, , .13, , .12, , 0, , 0, , 0, , 0, , 0, , 0, , 0, , .01, , 0, , 0, , 0, , .20, , .10, , 0, , .35, , .11, , .06, , .10, , 0, , 0, , ...

Exporting Access Tables to Comma-Delimited Text File
When I use the File -> Export function in Access, all it seems to do is duplicate the file. It doesn't seem to offer conversion to delimited text files. Can someone tell me how to do that kind of an export? Or does Access call it something else? Thanks, Scott On Jun 18, 10:19=A0am, Uncle Scotty <scottspieg...@gmail.com> wrote: > When I use the File -> Export function in Access, all it seems to do > is duplicate the file. It doesn't seem to offer conversion to > delimited text files. > > Can someone tell me how to do that kind of an export? Or does Access > call it something else? > > Thanks, Scott In the export dialog box you should be able to see a 'save as type' select list. Choose the appropriate format in there. You can also choose export by right clicking on the table you want to export. hth "Uncle Scotty" <scottspiegler@gmail.com> wrote in message news:7a02566f-8f32-4113-846f-80efb1f53986@w7g2000hsa.googlegroups.com... > When I use the File -> Export function in Access, all it seems to do > is duplicate the file. It doesn't seem to offer conversion to > delimited text files. > > Can someone tell me how to do that kind of an export? Or does Access > call it something else? > > Thanks, Scott A97 Select the table or query Select "Save as Export" from the File pull-down Send it to an External file or database Set Save as Type to Text Follow the prompts Do...

A free utility for direct copy of Tandem files into Ms Sql, Ms Excel and flat text files
It's an 'Ftp-like' command line utility. You can copy Tandem Enscribe Files and Sql Tables into MS Sql server tables, MS Excel files or flat text files. Filter can be applied on rows and columns, both for Enscribe Files and Sql tables. It's free. Downloadable from http://free.x3.hu/peak-tibor/ Tibor PE�K ...

FeatureClass Selectable Based on CSV (comma-delimited) Text File
I'm trying to create a selectable featureClass based on data in a comma-delimited text file. I keep getting an error that states, "The event table has no OID field or no unique value field, selections are not possible." I'm understanding that I need a column of type OID in order to have a 'selectable' layer. Can anyone point me to a code snippet that can demonstrate how to do this? Thanks. -ak http://php.mirrors.ilisys.com.au/manual/en/function.pg-last-oid.php "Ayron" <akitchen@lrs.com> wrote in news:1121271454.332107.216590 @g47g200...

many delimited test files into one text file
Each month I receive between 100-200 Delimited Text Files that I need to load into the same table. Using the filevar technique with the Data Step how should I read all of these text files in into 1 SAS Table using a single data step. Requires use of Unix LS command. -- Regards, John Burton Soddy Daisy TN ...

MS Access database with a file extension of .mde
Good day: Can you kindly assist me in opening a MS Access database with a file extension of .mde? I get an error message stating "This database is in an unrecognized format". Any help is greatfull. TIA This usually indicates an attempt to open an Access 2000 database in an earlier version. -- MichKa [MS] NLS Collation/Locale/Keyboard Development Globalization Infrastructure and Font Technologies This posting is provided "AS IS" with no warranties, and confers no rights. "mary" <chloemelmom@yahoo.com> wrote in message news:a0a64947.0311151242.4df0c8a3@posting.google.com... > Good day: > > Can you kindly assist me in opening a MS Access database with a file > extension of .mde? > > I get an error message stating "This database is in an unrecognized > format". > > Any help is greatfull. > > TIA "mary" <chloemelmom@yahoo.com> wrote in message news:a0a64947.0311151242.4df0c8a3@posting.google.com... > Good day: > > Can you kindly assist me in opening a MS Access database with a file > extension of .mde? > > I get an error message stating "This database is in an unrecognized > format". > > Any help is greatfull. > > TIA Best place to get answers is at the official MVP site: http://mvp.org Robyn Schulz, MVP A) That url isn't. B) If that's the best place to get answers, why do you hang out in a newsgroup designed to give ...

Re: many delimited test files into one text file
John, Take a look at: support.sas.com/techsup/technote/ts581.pdf HTH, Art -------- On Tue, 24 Feb 2009 22:54:59 -0500, John Burton <jrburtonsaspro@GMAIL.COM> wrote: >Each month I receive between 100-200 Delimited Text Files that I need >to load into the same table. Using the filevar technique with the >Data Step how should I read all of these text files in into 1 SAS >Table using a single data step. > >Requires use of Unix LS command. > >-- >Regards, >John Burton >Soddy Daisy TN ...

Re: many delimited test files into one text file #2
This example starts reading each file from FIRSTOBS=3. It use LS to pipe the names of CSV files that are opened and read into a single sas data set. data _null_; file 'murugesh101.csv'; put 'COL1,COL2,COL3,COL4,COL5'; put 'ClientName,Protocol,ShortName,StatusID,StudyID'; put 'ABC co ltd,ABC-3001,ABC3001,3,2314'; put 'DEF , D 123, D123, 4, 2256'; file 'murugesh202.csv'; put 'COL1,COL2,COL3,COL4,COL5'; put 'ClientName,Protocol,ShortName,StatusID,StudyID'; put 'GHI , GHI 999, GHI999, 9, 9999'; file 'murugesh102.csv'; put 'COL1,COL2,COL3,COL4,COL5'; put 'ClientName,Protocol,ShortName,StatusID,StudyID'; put 'ABC co ltd,ABC-3001,ABC3001,3,2314'; put 'GHI , GHI 999, GHI999, 9, 9999'; put 'ABC co ltd,ABC-3001,ABC3001,3,2314'; file 'murugesh204.csv'; run; filename FT55F001 '.'; data all(keep=source clientName Protocol ShortName StatusID StudyID); length csvname path command $256 source $16; path = catx('/',pathname('FT55F001'),'murugesh*.csv'); command = catx(' ','/usr/bin/ls -1',path); infile dummy1 pipe filevar=command truncover; input csvname $256.; infile dummy2 filevar=csvname eof=eof firstobs=3 dsd; source = scan(scan(csvname,-1,'/'),1,'.'); do while(1); informat ClientName $50. Protocol $8. shortname ...

delimited a text file
hello I would like to read a file like fg2235565 fg32356 hgjy232654 jsdvfkjvkjvnhnkhdvndfkvhdvnk jdfhkvhdklfvidfvkldfhvdvhfud to be able to do it i used textread('my_file.txt','%2s','delimiter') the thing is that the output is fg 22 35 56 5 fg 32 35 6 hg ..... so I would like to tell him to read 2 letters by two letters but that the output is not change.... how can I manage to do this ???? thank you very much remy Hi, it could be easier if you show the result you expect ! > fg2235565 fg32356 hgjy232654 > jsdvfkjvkjvnhnkhdvndfkvhdvnk > jdfhkvhdklfvidfvkl...

| Delimited Text File
Hi all, I have a | delimited text file (www.swandust.com\rpt_ccz.txt) that is delimited incorrectly. The heading reads as follows: CUS_NUM|GRP_NUM|BILL_TO|STATUS|STATUS_DATE|FIRM|SEC_ADD|ADD|CITY|PROV|CONTAC T|PC|PH_AC|PH_PRE|PH_NUM|PO_NUM|PO_DATE|AGREE_DATE|,SALES_NUM|SALES_NAME|FIR ST_DATE|ROUTE_ZONE|SIC|SRC|BUS_MON_1|DEL_FREQ_1|DOL_PERIOD_1|BUS_MON_2|DEL_F REQ_2|DOL_PERIOD_2 But if you look at a sample of the data below the headingit reads as follows: 207|153|G|W|01-Jan-1900DARE FOODS||2481 KINGSWAY DR|KITCHENER|ON|N2G 4G4||519|893|5500|| |01-Jan-1900|0|?|01-Jan-1900|U||ABCDEFGH|2|0|0.0...

Comma delimited file
I am trying to read comma delimited rows of text. The problem is that some fields may be encapsulated in "" - particularly the text fields but not numeric fields. Is there a simple efficient way to parse the fields with comma but also strip off the "" encapsulating some of the fields with php? The problem is the "" encapsulation is optional. Some fields will have it, some won't. Thanks Bertie Brink wrote: > I am trying to read comma delimited rows of text. The problem is that > some fields may be encapsulated in "" - particularly the t...

Error on opening a database: MS JEt database says someone tries to modify the file
Hello, I've received a databasefile from a friend, which seems to be corrupted. When i try to open the file it gives the error: The Microsoft Jet Database Engine has stopped the proces, since you and another user are trying to modify the same data (translated from dutch). I can't do anythinh with this file. Even importing from another database or Compact and Repairing gives me the same message. Has anyone got a clue how to save the data in this .mdb file. Greetings, Remco Remco Groot Beumer wrote: > Hello, > > I've received a databasefile from a friend, which seems to be corrupted. > When i try to open the file it gives the error: > The Microsoft Jet Database Engine has stopped the proces, since you and > another user are trying to modify the same data (translated from dutch). > > I can't do anythinh with this file. Even importing from another database or > Compact and Repairing gives me the same message. Has anyone got a clue how > to save the data in this .mdb file. > > Greetings, > > Remco > > if your friend doesn't have a backup, it's shot. There are companies that you can sent it to which specialize in getting data out of corrupt Access databases....but it'll cost you a pretty penny! ...

Error on opening a database: MS JEt database says someone tries to modify the file #2
Hello, I've received a databasefile from a friend, which seems to be corrupted. When i try to open the file it gives the error: The Microsoft Jet Database Engine has stopped the proces, since you and another user are trying to modify the same data (translated from dutch). I can't do anythinh with this file. Even importing from another database or Compact and Repairing gives me the same message. Has anyone got a clue how to save the data in this .mdb file. Greetings, Remco ...

Is it possible to load the excel file by using external table without converting it into csv comma delimited file
hi I am using oracle 9.2.0.1.0 version.. Is it possible to load the excel file by using external table without converting it into csv comma delimited file?? If yes then please tell me how can i achieve it I want to fileds terminated by cell for excel file. FIELDS TERMINATED BY <CELL> Thanx in advance Khurram Khurram wrote: > hi > > I am using oracle 9.2.0.1.0 version.. > > Is it possible to load the excel file by using external table without > converting it into csv comma delimited file?? > > If yes then please tell me how can i achieve it > > I want to fileds terminated by cell for excel file. > > > FIELDS TERMINATED BY <CELL> > > Thanx in advance > > Khurram Try opening an Excel spreadsheet in a text editor and you'll get your answer. HTH -g hi >gazzag wrote: > Try opening an Excel spreadsheet in a text editor and you'll get your > answer. > > HTH > > -g I opened the excel spreadsheet in a text editor but its showing me just ASCII code what i have to get idea?? Khurram On Tue, 11 Jul 2006 04:09:46 -0700, Khurram wrote: > hi > > I am using oracle 9.2.0.1.0 version.. > > Is it possible to load the excel file by using external table without > converting it into csv comma delimited file?? > > If yes then please tell me how can i achieve it > > I want to fileds terminated by cell for excel file. > > > FIELDS TERMINATED BY ...

concatenating multiple text files into one text file
Hello all, I have a huge number of text files like : spectrum1_300_315.csv spectrum1_315_331.csv spectrum1_331_364.csv spectrum1_364_386.csv spectrum1_386_401.csv ...................................... ....................................... each file contains some headerlines at the beginning and data in 2 columns. I want to concatenate the data of the files into one text file .Can anyone hel me pls?thank u in advance. ...

Converting A Unicode Text File to An ASCII Text File
We have a large number of text files built in Unicode that we need to be read by SAS. SAS does not read Unicode text. To convert the Unicode text to ASCII text we did the following: A Unicode text file adds an additional blank space to each character. To remove this blank character, you can use the code below. The hexidecimal value '00' is a blank character. So remove it using the SAS statement '00'x. The 'x' tells SAS you are reading hexidecimal. Use RECFM=N to tell SAS that you are reading a stream of data that will not conform to a typical file structure. SAS will treat the file as a very long single record. data _null_; infile 'c:\xmlfile.xml' recfm=n; file 'c:\ascifile.txt' recfm=n; input c $1.; if c ne '00'x then put c $1.; run; ...

How to populate column titles for y values when using Write to Measurement File Express VI with a tab delimited text-based .lvm file?
The .lvm file generated by the Write to Measurement File Express VI includes column heading titles for the y values, but they are a default value of "untitled".&nbsp; See attached example of a sample&nbsp;file when opened in Excel, the values in question are highlighted.&nbsp; Is there a way to specify the column titles when the file is written? &nbsp; This is a similar question to <a href="http://forums.ni.com/ni/board/message?board.id=170&amp;message.id=125937&amp;query.id=529265" target="_blank">this posting</a>, which received a work around response to use the Write to Spreadsheet File.vi,&nbsp;rather than a solution when using this VI. &nbsp;Message Edited by Hightop Raven on 05-02-2007 03:37 PM column_titles.gif: http://forums.ni.com/attachments/ni/170/245240/1/column_titles.gif the attached&nbsp;screen shot shows the variables going into an array to be sent to the write to measurement vi is it possible to add this NI_ChannelName attribute to the variables in this situation? write_to_file_screen_capture.gif: http://forums.ni.com/attachments/ni/170/245587/1/write_to_file_screen_capture.gif Sorry for the late reply.&nbsp; I was out of the office Friday. You can do it, but the code can get rather ugly.&nbsp; Under the hood, the dynamic data type (the dark blue wire) is an array of waveforms.&nbsp; In your case, the scalar values you convert to a dynamic data type are being con...

extracting delimited text from a file
I want to extract several lines from an html file that begin with pattern1 and end with pattern2 and ignore everything before pattern1 and after pattern2. Any snippets or suggestions will be greatly appreciated. Best, Christopher Christopher Glaeser wrote: > I want to extract several lines from an html file that begin with pattern1 > and end with pattern2 and ignore everything before pattern1 and after > pattern2. Any snippets or suggestions will be greatly appreciated. preg_match_all() is your friend http://www.php.net/preg_match_all example: <?php $data = 'p1p2p3p...

Writing delimited text files.
My code presently looks like this. data _null_ ; set merged (firstobs =2) ; FILE "J:\FSOR\sample.txt" dlm = '09'x ; put var1 var2 var3 var4 var5; run; This produces the output I want, which is the output file being tab delimited, commencing at observation 2 so the variable names are not printed. However, I have a very large dataset which means that typing out all the variable names in the put statement is not practical. I have tried substituting the list of variables for put _all_ but this just prefixes the variable name and = before each value. e.g var1...

Tab Delimited Text Files
I recently purchased the MS Access 2007. I have used MS Access 2003 for years. I need to transfer a table in a Tab Delimited Text File format and do not see where2007 has a TransferText function which gives you the tab delimited option. Is TransferText not available with 2007 or have I not looked deep enough ? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200801/1 On the External Data tab of the ribbon, in the Export chunk, click the Text File icon. If you are having trouble seeing TransferText in macro design view, depress the Show All Actions icon on the Show/Hide chunk of the Design ribbon. You can also use TransferText in code. If no code runs at all, specify your database's folder is a trusted location under: Office Button | Access Options | Trust Center | Trust Center Settings -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "destinman via AccessMonster.com" <u18648@uwe> wrote in message news:7da2f3c210165@uwe... >I recently purchased the MS Access 2007. I have used MS Access 2003 for > years. I need to transfer a table in a Tab Delimited Text File format > and > do not see where2007 has a TransferText function which gives you the tab > delimited option. Is TransferText not available with 2007 or have I not > looked deep enough...

Web resources about - COMMA delimited TEXT FILE - comp.databases.ms-sqlserver

Resources last updated: 3/22/2016 11:19:04 AM