Import CSV Files into Excel

Hi

I produce a CSV file from a mainframe application which users must import into Excel.

I need to know how I can influence the import wizard into selecting a numeric field with for example leading zeros as a text Column.

I know I can use the import wizard to manually set the desired columns to text, or write a macro to set the column to text, but as I wont be physically doing the import, I want to make it as easy as possible for the user who receives the comma delimeted mainframe file, to import into a blank worksheet on his workstation.

I have tried inserting a single leading apostrophe into the csv file for a value that i wish to be treated as text, [as you can when you type into a cell ] but that imports the apostraphe rather than setting the cell to text.

I would welcome any suggestions as to how I could achieve this

Thanks

I have a workbook which imports date from several csv files in a fixed directory. I've been working with the same files with no problems for a day. After closing the excel workbook down overnight, and re-opening it the next day i suddenly get an error message when i try to import the data.

Run-time error '1004'
Excel cannot find the text file to refresh this external data range.
Check to make sure the text file has not been moved or renamed, then try the refresh again.

I've made no changes to the excel sheet or the data files, they are all in the same directories as yesterday but I cannot get passed this error message.

The data import is performed using a vba macro (which worked perfectly yesterday) now it's Kaput.

Anyone have any ideas?

Hi!
Searched a lot, but found no solution to my problem.
I am trying to use macro to import *.csv file data from internet using Data->Import extrenal data->import data. If I use macro recorder to record the procedure and use it again, it is not working. I know from somewhere that it is possible to download .csv file, save it and then import data using macro (I dont know exactly how, but there is possibility to do that). The question is - is it possible to import data using one macro and not saving file to the disc?
If needed, here is the link to CSV file:
"http://www.baltic.omxnordicexchange.com/market/?start_d=1&start_m=1&start_y=2000&end_d=24&end_m=7&end_y=2008&pg=nontradeddetails&instrument=LV00004 00323&tab=price&lang=lv&currency=0&date=&pg=nontradeddetails&pg2=funddetail&downloadcsv=1&csv_style= baltic".
Af course I need that data be delimited (using semicolon).

I will also use procedure that cheks in range A:A for different "instrument=LV0000400323" and changes the link, then creates (if its not already created) sheet and names it "instrument=LV0000400323", and copy data to that sheet... and so on. But for that I know solution... at least I think so

Thanx in advance!

I have a 700+ mb csv file. I would like to be able to do a data import that is filtered so I can get only what I need out of the file.

Is there a way to do that or a good software tool?

Hey All,

My goal:
- I have an existing workbook ("MAIN.xlsm").
- In that workbook I have a user interface worksheet ("INTERFACE") that has a command button that I want to "Import Data" into a worksheet ("DATA1")
- Upon clicking that command button, I would like to have a box that pops up (similar to the "Excel Button > Open" style) to select a file (file name changes regularly with no specific order) and then import all the contents of that file into cell "A1" of worksheet "DATA1".

It doesn't have to be exactly this, but something that is fairly user friendly. I have figured out how to do it by defining the file path in VBA, but not while allowing a user to select that path/file visually/easily.

Thoughts?

I need some help writing a macro that will open a csv file from a URL, then import the data contained in that csv file into one of the worksheets. I will already know the complete URL. I do not need to download or save the CSV file, just pull the data into my workbook.

I would like to make this as transparent to the user as possible. Basically, the user will click on a command button to initiate the process, and be informed once it has completed, barring any error alerts.

Can anyone point me in the right direction, perhaps offer some similar code that i can modify? Thank you.

Hello everyone,

I need your help on a project which involves, among others, importing a .csv file which has empty rows and columns.
Here's a code I found on the net and worked on:

VB:

Sub import_from_csv() 
     'Imports text file into Excel workbook using ADO.
     'If the number of records exceeds 65536 then it splits it over more than one sheet.
    Dim strFilePath As String, strFilename As String, vFullPath As Variant 
    Dim lngCounter As Long 
    Dim oConn As Object, oRS As Object, oFSObj As Object 
    Dim msg As String 
    Dim style As Integer 
     
    msg = "Doriti sa importati datele din fisierul .csv?" 
    style = vbQuestion + vbYesNo 
    If MsgBox(msg, style, "Import date") = vbNo Then Exit Sub 
     'Get a text file name
    vFullPath = Application.GetOpenFilename(filefilter:="CSV Files (*.csv),*.csv," & _ 
    "Text files (*.txt),*.txt," & _ 
    "All files (*.*), *.*") 
     
    If vFullPath = False Then Exit Sub 'User pressed Cancel on the open file dialog
     'Application.ScreenUpdating = False
     
     'This gives us a full path name
     'We need to split this into path and file name
    Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT") 
    strFilePath = oFSObj.GetFile(vFullPath).ParentFolder.Path 
    strFilename = oFSObj.GetFile(vFullPath).Name 
     
     'Open an ADO connection to the folder specified
    Set oConn = CreateObject("ADODB.CONNECTION") 
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & strFilePath & ";" & _ 
    "Extended Properties=""text;HDR=Yes;FMT=Delimited;IMEX=1""" 
    Set oRS = CreateObject("ADODB.RECORDSET") 
     
     'Now actually open the text file and import into Excel
    On Error Goto Eroare 
    oRS.Open "SELECT * FROM [" & strFilename & "]", oConn, 3, 1, 1 
     
    While Not oRS.EOF 
        Sheets("chargereportadmin").Select 
        ActiveSheet.Range("$A$1").CopyFromRecordset oRS, 65536 
    Wend 
     
    oRS.Close 
    oConn.Close 
    MsgBox "Datele au fost importate din fisierul .csv", vbInformation, "Import finalizat" 
    Exit Sub 
Eroa  
    MsgBox "Operatiunea de importare a datelor a esuat." & vbCr & _ 
    "Este posibil ca fisierul sa fie deschis de un alt utilizator." & vbCr & _ 
    "Va rugam sa inchideti fisierul si sa reincercati.", vbCritical, "Import esuat" 
    Exit Sub 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



What I need to do is import the data from the .csv file but I would like to skip the blank rows and columns.
Any help would be very much appreciated.

Thank you.

I really need some help -- I've been searching forums for 3 days and have seen similar problems but none of the solutions have solved my problem. I'm writing a macro for multiple users (most have Excel 2003, some have 2007) to open a .csv file, do some cleanup, save in Excel, then open a prn file, merge some columns from there to the first csv and save the newly created file as final Excel file. The csv files and the prn files will always have different names. I did change the "Name" part of the .Add method to a standardized name so that I'll be able to reference it later -- I hope that didn't mess anything up.
My problem is opening that csv file. I'm getting a "Run-time error 1004" error on "Method 'Range' of object'_Global' failed. It hangs up on the "With ActiveSheet.... Destination:=Range" line of code. The csvFileName variable is getting populated so I know that part is okay. So I'm guessing it's because Excel doesn't know where to deposit the file it just picked up even though my code is telling it to put it in Cell A1. (I'm writing this in 2003 and then going to create the macro for 2007 secondly.) I don't have a worksheet open when the macro runs, could that be part of the problem? Even when I try having one open, I still error out.
Here's the beginning part of the code.....(the rest works) Thank you!
(this is the first time I've ever posted anywhere so I apologize for any social faux pas.)

VB:

 
================== 
Sub CW2_Input() 
     '
     ' CW2_Input Macro
     ' Cleans up IOT raw data. Input box asks for Store Number and MFR code.
    Dim LR As Long 
    Dim strSTID As String 
    Dim strLINCODE As Variant 
    Dim csvFilename As String 
    Dim Response As Variant 
    Dim IOTfileName As String 
     '===================
     
    csvFilename = Application _ 
    .GetOpenFilename("Comm Separated Value Files (*.csv), *.csv") 
    If csvFilename <> False Then 
        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & csvFilename _ 
            , Destination:=Range("$A$1")) 
            .Name = "IOT Data" 
            .FieldNames = True 
            .RowNumbers = False 
            .FillAdjacentFormulas = False 
            .PreserveFormatting = True 
            .RefreshOnFileOpen = False 
            .RefreshStyle = xlOverwriteCells 
            .SavePassword = False 
            .SaveData = True 
            .AdjustColumnWidth = True 
            .RefreshPeriod = 0 
            .TextFilePromptOnRefresh = False 
            .TextFilePlatform = 437 
            .TextFileStartRow = 1 
            .TextFileParseType = xlDelimited 
            .TextFileTextQualifier = xlTextQualifierDoubleQuote 
            .TextFileConsecutiveDelimiter = False 
            .TextFileTabDelimiter = True 
            .TextFileSemicolonDelimiter = False 
            .TextFileCommaDelimiter = True 
            .TextFileSpaceDelimiter = False 
            .TextFileColumnDataTypes = Array(2, 2, 2, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _ 
            2, 2, 2, 2, 1, 1, 1, 2, 2, 9) 
            .TextFileTrailingMinusNumbers = True 
            .Refresh 
        End With 
    End If 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

Hi all

I am trying to import some csv files so I can combine them, but am having probs with the filename and location. Any thoughts. Thx in advance

VB:

Sub test() 
    Dim wsName As String 
     
    wsName = ActiveCell 
    Sheets("Data").Select 
    With ActiveSheet.UsedRange 
        LastRow = .SpecialCells(11).Row 
    End With 
     
    With ActiveSheet.QueryTables.Add(Connection:="TEXT; &thisWorkbook.Path &" \ " & wsName &", Destination:=Range("A" & LastRow)) 
        .Name = wsName 
        .FieldNames = True 
        .RowNumbers = False 
        .FillAdjacentFormulas = False 
        .PreserveFormatting = True 
        .RefreshOnFileOpen = False 
        .RefreshStyle = xlInsertDeleteCells 
        .SavePassword = False 
        .SaveData = True 
        .AdjustColumnWidth = True 
        .RefreshPeriod = 0 
        .TextFilePromptOnRefresh = False 
        .TextFilePlatform = 850 
        .TextFileStartRow = 1 
        .TextFileParseType = xlDelimited 
        .TextFileTextQualifier = xlTextQualifierDoubleQuote 
        .TextFileConsecutiveDelimiter = False 
        .TextFileTabDelimiter = True 
        .TextFileSemicolonDelimiter = False 
        .TextFileCommaDelimiter = True 
        .TextFileSpaceDelimiter = False 
        .TextFileColumnDataTypes = Array(1, 4, 4, 4, 1, 1) 
        .TextFileTrailingMinusNumbers = True 
        .Refresh BackgroundQuery:=False 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines