How can I import CSV with Carriage Return?

  • Permalink
  • submit to reddit
  • Email
  • Follow


Hi, I have a set of comma delimited values in a text file.  They import
fine.  However, they are all in one record.  How can I get each value
on one record?  For example, say I have:

toys, books, shoes, houses

If I import, I get them all on one row.

I want each one on a separate row:

toys
books
shoes
houses

Any ideas?

Thanks for any help.

0
Reply needin4mation (120) 3/10/2006 3:38:38 AM

See related articles to this posting


On 9 Mar 2006 19:38:38 -0800, needin4mation@gmail.com wrote:

You would have to process this file using regular file i/o functions
such as Open, Line Input #, etc., and then add each word to the table
using an Append query or a recordset.AddNew.

-Tom.


>Hi, I have a set of comma delimited values in a text file.  They import
>fine.  However, they are all in one record.  How can I get each value
>on one record?  For example, say I have:
>
>toys, books, shoes, houses
>
>If I import, I get them all on one row.
>
>I want each one on a separate row:
>
>toys
>books
>shoes
>houses
>
>Any ideas?
>
>Thanks for any help.

0
Reply no.spam.tom7744 (2731) 3/10/2006 4:05:03 AM

You can use File I/O as Tom suggests or you can import to a table and then
open a recordset on the imported table and run through the fields apending
the values to your target table then drop the imported table.

e.g. Where the imported text file is in table MyText the target table is
MyTarget the Target field is MyTargField

Using DAO
    dim db as DAO.Database
    dim rsSce as DAO.Recordset
    dim rsTarg as DAO.recordset
    dim fldSce as DAO.Field

    set db = Currentdb

    set rsSce = db.Openrecordset(SELECT * FROM MyText)
    set rsTarg = db.Openrecordset(SELECT * FROM MyTarget)

    with rsSce
        Do Until .EOF
            for each fldSce in .Fields
                rsTarg.AddNew
                rsTarg.Fields(MyTargField) = fldSce.Value & ""
                rsTarg.UpDate
            Next
            .MoveNext
        Loop
        .Close
    End With

    rsTarg.Close

    Set rsSce = Nothing
    Set rsTarg = Nothing
    Set db = Nothing

-- 

Terry Kreft


<needin4mation@gmail.com> wrote in message
news:1141961918.050391.292160@u72g2000cwu.googlegroups.com...
> Hi, I have a set of comma delimited values in a text file.  They import
> fine.  However, they are all in one record.  How can I get each value
> on one record?  For example, say I have:
>
> toys, books, shoes, houses
>
> If I import, I get them all on one row.
>
> I want each one on a separate row:
>
> toys
> books
> shoes
> houses
>
> Any ideas?
>
> Thanks for any help.
>


0
Reply terry.kreft (988) 3/10/2006 11:50:48 AM
comp.databases.ms-access 42294 articles. 10 followers. Post

2 Replies
513 Views

Similar Articles

[PageSpeed] 44


  • Permalink
  • submit to reddit
  • Email
  • Follow


Reply:

Similar Artilces:

osql import inserting carriage returns?
I'm trying to run an import of some data that has unix style carriage returns throughout (ie, 0x0A, or \n). When i use osql to import the data, it shows up in the SQL Server 2005 database as 0x0D 0x0A or \r \n. I do not want this behaviour - the import file is very large and has ddl at the beginning. Can anyone suggest a more suitable utility or method for importing this data that will preserve the original format? Or perhaps my database is not configured correctly? Thanks, Bob On Wed, 15 Oct 2008 09:03:57 -0700 (PDT), "bobdurie@gmail.com" <bobdurie@gmail.com> wrote: ...

Import CSV can not understand string correctly
In my CSV file, I have string fields like 3D22 or so. While I imported the CSV file in mathemtaica, the 3D22 string was converted to number 3x10^22. How dose this happened??? How can I avoid this kind of transformation and keep my original string when Import a CSV file? ...

Carriage Return problems when importing from a Word document
I am trying to import data from a Word document into an Access table with VBA. The Word document is a form which has various fields. One of the fields is a field for notes. In some cases, this note field contains carriage returns. When I import a note field that has carriage returns, what shows up in the Access field are vertical black lines where the carriage return should be. I have done searches on this problem, and seen a number of "solutions", one from Microsoft suggests turning the carriage returns into tabs, another solution simply eliminates the carriage returns altogether...

Can't get carriage returns in div
I have a page with a div on it. The div displays a user comment. When the user logs into this page, their current comment is pulled from a db and displayed in the div. The user can edit the comment through a pop-up that contains a textarea. When the user hits OK on the pop-up, the text in the textarea is sent to a function on the main page. The function inserts the text into the div's text node. Please don't ask why I'm making this so complicated - there are other things going on on the page and the pop-up that are irrelevant to my problem. Everything works perfectly except ...

How can I import 2 csv files into 1 table
I will try to explain my situation. I know that it is hard to offers solutions without fully understanding what people would like to achieve. I receive 2 csv files every month. The csv files change name each month. Each file contains 13 columns and around 20k rows. In each file, the first row is the heading. I have created a database that helps me sort out the data. Prior to importing into the database, I have been copying the csv data into one xls file. I then run an Excel macro to format the data. The macro does a few things. The first thing it does is remove any blank spaces in the text o...

Re: Import CSV can not understand string correctly
On 9/24/13 at 4:16 AM, itais@wolfram.com (Itai Seggev) wrote: >On Sat, Sep 21, 2013 at 04:36:10AM -0400, Joug Raw wrote: >>In my CSV file, I have string fields like 3D22 or so. While I >>imported the CSV file in mathemtaica, the 3D22 string was converted >>to number 3x10^22. >>How dose this happened??? How can I avoid this kind of >>transformation and keep my original string when Import a CSV file? >You can set the option "Numeric"->False which will keep everything >as strings. Numbers will need to be explicitly converter to ...

How can I detect a carriage return using java.net
Hello I have code a bit like this: BufferedReader bis = new BufferedReader(new InputStreamReader(tserverSocket.getInputStream())); // Here I write some data - getting a response - some lines of text returned boolean more = true; while(more) { String line = bis.readLine(); if (line == null) more = false; else System.out.println(line); } But code never gets out of while loop. I assumed when there was no more input it would return null, so more would be set to false and it would exit loop. I want to test for a carriage return. (I know text is in ASCII format, and ...

Any email program that can import messages via a csv file?
Does anyone know of an email program that can import messages via a csv file? I know most of them can import an address book that way but I need to be able to import messages. I have them in that format and can't get them into any other. thanks, This is a MIME GnuPG-signed message. If you see this text, it means that your E-mail or Usenet software does not support MIME signed messages. The Internet standard for MIME PGP messages, RFC 2015, was published in 1996. To open this message correctly you will need to install E-mail or Usenet software that supports modern Internet standards. --...

Eliminate embeded carriage return in CSV file under version 8
I have a name and address file in which the address field contains embeded carriage returns using the infile statement results in a dataset with multiple data records addr addr addr I want to replace the carriage return with a space to generate one address field per name can anyone suggest a solution. TIA Adam <worldtraveller999@GMAIL.COM> wrote: > I have a name and address file in which the address field contains > embeded carriage returns using the infile statement results in a > dataset with multiple data records > > addr > addr > addr > > I want to repl...

how can I import a csv or tsv table as a table in a latex document?
considering the first row is the header. anyone? avilella wrote: > considering the first row is the header. > > anyone? have you had a look at the csvtools package? -- /daleif (remove RTFSIGNATURE from email address) LaTeX FAQ: http://www.tex.ac.uk/faq LaTeX book: http://www.imf.au.dk/system/latex/bog/ (in Danish) Remember to post minimal examples, see URL below http://www.tex.ac.uk/cgi-bin/texfaq2html?label=minxampl http://www.minimalbeispiel.de/mini-en.html Lars Madsen schrieb: > avilella wrote: >> considering the first row is the header. >> >&...

Re: Eliminate embeded carriage return in CSV file under version 8
Adam <worldtraveller999@GMAIL.COM> wrote: > I have a name and address file in which the address field contains > embeded carriage returns using the infile statement results in a > dataset with multiple data records > > addr > addr > addr > > I want to replace the carriage return with a space to generate one > address field per name can anyone suggest a solution. The following rambling assumes that you're working on a Win32 box. If not, then you need to correct me. Are these 'carriage returns' or 'hard returns'? I mean, when you look at...

How can I make the carriage return have the desired effect and not take me to the next field?...
Good evening folks, can anyone advise me on how to resolve the above? Cheers Andy Andy, Set the Control's Enter Key Behavior property to Default It's on the property sheet's Other tab. -- Fred Please reply only to this newsgroup. I do not reply to personal e-mail. "Murdoch" <anon@blueyonder.co.uk> wrote in message news:ToO3b.42$j97.3@news-binary.blueyonder.co.uk... > Good evening folks, can anyone advise me on how to resolve the above? > > Cheers > > Andy > > ...

Re: Eliminate embeded carriage return in CSV file under version 8 #2
Please note that in the second example if a CRLF is on the last variable you are out of luck. Paul Choate DDS Data Extraction (916) 654-2160 -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Paul Choate Sent: Friday, June 03, 2005 11:09 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: Eliminate embeded carriage return in CSV file under version 8 Adam - If you want to replace all CR's that are not part of a CRLF with a space ('20'x) then this will do what you want: data _null_; infile 'dirty.txt' recfm=n EOF=end; file '...

Can I construct this process in SAS session (unzip csv files,import,name dataset if and delete upzipped files) ?
Hi, Can I do this in SAS? 1.Unzip a zip file (containing various delimited .csv files) 2.Import the files into SAS. 3.Name the dataset depending on if the file name contains 'xxx' 4.delete the .csv files after importing Apologies in advance as I am not very experienced with loops and arrays if that is what's needed. Thanks Lee "SAS User" <sasuser2010@googlemail.com> wrote in message news:ec00a638-4f85-4d1e-8a9a-6a0db42ddf07@c33g2000yqm.googlegroups.com... > Hi, > > Can I do this in SAS? > > 1.Unzip a zip file (containing various delimited .csv...

Re: How can labview update the string control (text-edit box) after we have pressed the carriage return key on the keyboard during text-editing within that box?
Hello Alberto, &nbsp; I have trouble to edit an exited text file and save it after the content changed. Can you please&nbsp;help me out? &nbsp; Thanks, Jason&nbsp; ...

Help: Delete a single carriage return in a file, but not a double carriage return?
I'd like to use perl to remove all single carriage returns, but leave all double carriage returns intact in a text file. For example, the following: foo bar baz baz bar foo baz baz baz foo bar baz should become: foo bar baz baz bar foo baz baz baz foo bar baz It sounds simple, but I haven't been able to figure out how to do this. Can anyone give me a hand? Thanks, Steve Quoth Steve Anderson <steveo@member.fsf.org>: > I'd like to use perl to remove all single carriage returns, but leave all > double carriage returns intact in a text file. > > ...

Importing a CSV file; imports all but one field
Hi there, I'm running access 2002 SP-2. I'm trying to import a CSV file where everything except the last (end, and most recently added) field will import. I can open the file in notepad, and it has data in that field. Access has chosen the default data type as Double. (The data is: 00.000, or substitute '0' for any number) There is only 19,000 rows, and it imports in seconds, but not that last field. The field names match. The field data types match. I am using import specifications so it should throw up errors if there was an inappropriate data-type, or wrong field name....

Importing from Excel: Can I specify columns to import?
I know that when I import from Excel, I can use CELLRANGE to specify exactly which cells I wish to import, as in the example below. GET DATA /TYPE=XLS /FILE='/data/sales.xls' /SHEET=name 'June Sales' /CELLRANGE=range 'A1:C3' /READNAMES=on. But suppose I know that I want certain columns (A to C to be consistent with the example above), but not the number of rows. I just want to import all rows that have data. Can that be done? I ask because in a file I just imported (with CELLRANGE = FULL), a whole bunch of empty columns came along, and had ...

TIP #261: Return Imported Commands from [namespace import]
[Editors note: A big apology to Martin Lemburg for sitting on this so long. I have no good excuse.] TIP #261: RETURN IMPORTED COMMANDS FROM [NAMESPACE IMPORT] ============================================================ Version: $Revision: 1.2 $ Author: Martin Lemburg <martin.lemburg_at_gmx.net> State: Draft Type: Project Tcl-Version: 8.5 Vote: Pending Created: Tuesday, 20 December 2005 URL: http://purl.org/tcl/tip/261.html WebEdit: http://purl.org/tcl/tip/edit/261 Post-History: --------------------------------...

carriage return
Hi, This may not so much related to this group than xslt group, but just desparately need to get the problem solved. So here it goes. I have an XML document like the following. I use xsl to output the html code. However, <br/> is always shown as is instead of a carriage return. I've tried using &lt;br/&gt; and &#x0D and &#x0A. None of these escape sequence worked. They all show up as typed. In addition, the carriage return in the content would also cause the javascript "unterminated string constant" error. I would appreciate if anyone could help. Than...

Carriage returns
I am formatting a plain text file into HTML and trying to find the carriage returns with little success... <?php $s =3D file_get_contents('./test.txt') $search =3D array ('\n\n'); $replace =3D array ('<p></p>'); echo preg_replace($search, $replace, $s); ?> I have also tried \n, \r, \n\r etc... in place of \n\n but to no avail. Can anyone help me with this? I know text files aren't an ideal format and a database would be easier, but the site will be used by people with no computer knowledge what so ever, and it is easier to show them how to use...

carriage return
Hi, I got a file made by one line like this : <A><a>.<a/><b>.<b/>...<A/><B>...<B/><C>...<C/><A>...<A/><C>...<C/><C>...<C/> .... and I would like to put carriage return between XML between main blocks : <A><a>.<a/><b>.<b/>...<A/> <B>...<B/> <C>...<C/> <A>...<A/> <C>...<C/> <C>...<C/> .... How can I do that by using shell commands ? Also, anybody knows of an XML parser in shell, or why not in perl ? Th...

Carriage Return added during return of large string from class method
Say, this is a doosey: I'm constructing a string of markup for a CGI script, and I'm deciding to convert to print as I go rather than all at once, so I code, then start debugging, and to debug I dump markup. I then find that markup that looks good and clean just before I return from my class function (called by another class function if that's important) ends up having sections polluted with carriage returns (not all of it, just those newlines which precede each line of one of the three concatenated strings from the three files which I uploaded from my primary files (javascript ...

importing csv
New and not a power user. I provide conference reg services. My client wants to import post conference info back into their Access db from csv file I provide. I'm quite sure anyone who knows their way around a db can do this for her. True? Look at TransferText method in the Help file. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications Over 1175 users have come to me from the newsgroups requesting help resource@pcdatasheet.com <bruce@destinatio...