How do I CONVERT .CSV files into Word or Excel?

  • Involved
  • Student
  • Student
  • User avatar
  • Posts: 89
  • Loc: New York City\Metro Area

Post 3+ Months Ago

Hi...I have a file of people who have ordered through my website and would like to put them into a readable format in Word or Excel, or both. I imagine Excel will give me more options for sorting. The only solutions I have found are either commercial (i.e., pay to play) or so techie that they are beyond my knowledge horizon.

Solutions? Ideas? Thanks! David (aka Involved) :D
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • Alkatr0z
  • Mastermind
  • Mastermind
  • Alkatr0z
  • Posts: 1883
  • Loc: Adelaide, Australia

Post 3+ Months Ago

I downloaded some server logs recently in .csv format and they opened just fine in Excel. File -> Open
Use the drop down list to select csv files and go for it. Otherwise using Excel's Import should work.
That works for one file or if you are happy doing it manually.
Otherwise you can always write a macro to do it.
Shouldn't be too hard really, example below:

Dim FirstRecord as String
Dim SecondRecord as String
' You can put any extra records in by Dim'ing them here.
Open "C:\test.csv" For input as #1
Do until EOF(1)
' You can add any more records in by adding a , then the variable you made for the record. This reads the data in and uses the ,'s in the file as delimiters
File Input #1, FirstRecord, SecondRecord

' Places the first variable data into the active cell
ActiveCell.FormulaR1C1 = FirstRecord

' Moves the active cell one colum to the right
ActiveCell.Offset(0,1).Range("A1").Select

' Places the second variable data into the active cell
ActiveCell.FormulaR1C1 = SecondRecord

' Moves the active cell down one row and one column to the left so it is ready for the next recordset
ActiveCell.Offset(1,-1).Range("A1").Select
Loop

Close #1

Haven't tested that but it gives you an idea of what it would entail. With more information on things like how many records etc I could give a better example of how to do it.
  • Involved
  • Student
  • Student
  • User avatar
  • Posts: 89
  • Loc: New York City\Metro Area

Post 3+ Months Ago

Alkatr0z...

Thanks so much "Mate" :D

I just realized that I do not have the full Excel program, only the Viewer, which will not read the .csv file format.

I gotta admit that, while I'm constantly helping friends who need computer advice and help, I'm nowhere when it comes to macros, much less writing them.

BTW, I visited your fantastic country WAY back in '77 (1977, not 1877, LOL). I was there as a journalist and loved it, though I was limited to Sydney, Melbourne, Cairns and the Ayers Rock area of the Outback. I've always wanted to go back--with my wife this time--but the cost and the length of the trip are daunting. When I visited, I was able to sit in First Class, which was particularly nice because I am 6-feet, 3 inches (190.5 cm) tall. Sitting in economy class seats for 24 or so hours is terribly unappealing. ; )

Thanks very much for your help!

David (aka Involved) :D

// MOD Edit: Removed portion of e-mail addresses
  • grinch2171
  • Moderator
  • Genius
  • User avatar
  • Posts: 6805
  • Loc: Martinsburg, WV

Post 3+ Months Ago

Posting people's e-mail addresses on a public forum is a bad idea.
  • Involved
  • Student
  • Student
  • User avatar
  • Posts: 89
  • Loc: New York City\Metro Area

Post 3+ Months Ago

Oh man, you are SO right. Dumb, dumb, dumb. Can I delete the message, or can you? I should have sent a private message, I imagine. Dumb.
  • Involved
  • Student
  • Student
  • User avatar
  • Posts: 89
  • Loc: New York City\Metro Area

Post 3+ Months Ago

Oh, I see you did remove parts of the addresses. Whew! Thanks! Will it be okay to send a private message?
  • grinch2171
  • Moderator
  • Genius
  • User avatar
  • Posts: 6805
  • Loc: Martinsburg, WV

Post 3+ Months Ago

It is okay to send PM's. I would just try and keep people's e-mail addresses out of the data you send.
  • Alkatr0z
  • Mastermind
  • Mastermind
  • Alkatr0z
  • Posts: 1883
  • Loc: Adelaide, Australia

Post 3+ Months Ago

Don't need the fields anyway. Just the number of fields and what sort of data they are holding and you provided that.
Up the top right hand side of your post is the "Edit" button. I'd suggest editing all the names etc out.

I'll work up a macro in a moment.
  • Alkatr0z
  • Mastermind
  • Mastermind
  • Alkatr0z
  • Posts: 1883
  • Loc: Adelaide, Australia

Post 3+ Months Ago

Okay ran this with a test file and it looked to work fine.

Code: [ Select ]
  Dim Record(24) As String
  Dim LoopCount As Integer
  Dim Filename as String
  
  Filename = InputBox("Please type in the full path and name to the csv file. eg C:\test.csv")
  If Len(FileName) > 0 Then
  Open FileName For Input As #1
  Do While Not EOF(1)
    Input #1, Record(0), Record(1), Record(2), Record(3), Record(4), Record(5), Record(6), Record(7), Record(8), Record(9), Record(10), Record(11), Record(12), Record(13), Record(14), Record(15), Record(16), Record(17), Record(18), Record(19), Record(20), Record(21), Record(22), Record(23)
    For LoopCount = 0 To 24
      ActiveCell.FormulaR1C1 = Record(LoopCount)
      ActiveCell.Offset(0, 1).Range("A1").Select
    Next LoopCount
    ActiveCell.Offset(1, -25).Range("A1").Select
  Loop
  Close #1
  End If
  1.   Dim Record(24) As String
  2.   Dim LoopCount As Integer
  3.   Dim Filename as String
  4.   
  5.   Filename = InputBox("Please type in the full path and name to the csv file. eg C:\test.csv")
  6.   If Len(FileName) > 0 Then
  7.   Open FileName For Input As #1
  8.   Do While Not EOF(1)
  9.     Input #1, Record(0), Record(1), Record(2), Record(3), Record(4), Record(5), Record(6), Record(7), Record(8), Record(9), Record(10), Record(11), Record(12), Record(13), Record(14), Record(15), Record(16), Record(17), Record(18), Record(19), Record(20), Record(21), Record(22), Record(23)
  10.     For LoopCount = 0 To 24
  11.       ActiveCell.FormulaR1C1 = Record(LoopCount)
  12.       ActiveCell.Offset(0, 1).Range("A1").Select
  13.     Next LoopCount
  14.     ActiveCell.Offset(1, -25).Range("A1").Select
  15.   Loop
  16.   Close #1
  17.   End If


To set it up just go to the Macros menu. Record a new macro. Then stop recording
View macros and select the macro you just created
Paste that code in between the 's and the End Sub

There are two limitations. One you can't use " in any of the field names. So for example that excludes a 'house name' instead of a house number in the address. Or at least excludes it if you want to put quotes around it.

Secondly you should select the first cell which you want the imported data to go into before running the Macro.

Edit: Those record(11), record(12) etc should all be on the same line. They should do so anyway but want to make sure they do.
  • Involved
  • Student
  • Student
  • User avatar
  • Posts: 89
  • Loc: New York City\Metro Area

Post 3+ Months Ago

You're the best!

I'm going to read and re-read your instructions before I try installing and running the macro.

Am I correct that: (1) this will run with XLViewer; (2) it should run with Access, and (3) it will not run with Word (even if I create a table).

It's 40 minutes BEFORE midnight where you are, so I don't want to bother you further. (I'll bother you AFTER midnight, LOL).

Thanks again
  • Alkatr0z
  • Mastermind
  • Mastermind
  • Alkatr0z
  • Posts: 1883
  • Loc: Adelaide, Australia

Post 3+ Months Ago

Unfortunately the answer to the last two questions is no and with XLView I'm not sure but I consider it unlikely that you can use VBA with it. I saw where you said you had XL Viewer but thought you were going to get a full Excel to do it with.
Access VBA & Word VBA are both different unfortunately from the way that Excel's VBA works.
I'm not much of a Access VBA programmer, and I have done a very small amount in Word, general adding lines into a document according to what someone wants to add in from pre recorded lines. From what I've heard and seen Access's VBA is quite different from Word or Excel whereas Word & Excel are still fairly close together in functionality and how you do things.

If you have Access and Word how come you don't have Excel ?

Post Information

  • Total Posts in this topic: 11 posts
  • Users browsing this forum: No registered users and 37 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.