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 42166 articles. 10 followers. Post

2 Replies
433 Views

Similar Articles

[PageSpeed] 0

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

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