Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 22

Thread: Help import from csv file

  1. #1

    Help import from csv file

    Hello,

    i was wondering if someone would look into my
    file because i can't get the csv file added into my xmls file

    it contains ,,,,
    and i can't get it into the correct columns and i also like when we import other files it will be added under it and not overwrite the cells

    i used a code from dragonwood
    thank for the sharing

    if anyone can help please

    we have lots to ask for but first comes first

    thanks
    Attached Files Attached Files

  2. #2
    Sorry i forgot to remove the password of the file
    here's the one without password.


    'because i can't edit my post'
    Attached Files Attached Files

  3. #3
    VBAX Expert
    Joined
    Aug 2004
    Posts
    744
    Location
    change your csv file extension to txt
    Workbooks.Open sTemperature, , , 4, , , , , ";"

  4. #4
    Quote Originally Posted by JKwan View Post
    change your csv file extension to txt
    Workbooks.Open sTemperature, , , 4, , , , , ";"
    Hello JKwan,

    awesome, so whats the '4' be for?
    and how can i add more after the existing records?
    like when we have more files to import
    the data will be overwritten and that's not really the idea

    Thanks

  5. #5
    Hello,

    updated, this seems to be working
    now my other question will be
    how can i only select the columns i really want to import
    instead all the columns?

    like only B C H
    to my sheet B C F

    Dim wbMaster As Workbook
        Dim wbTemperature As Workbook
        Dim sTemperature As String
        Dim wsImport As Worksheet
        Dim destCell As Range
    
    
        Set wbMaster = ThisWorkbook
        Set wsImport = wbMaster.Worksheets("sheetlist2")
        Set destCell = Worksheets("sheetlist2").Cells(Rows.Count, "A").End(xlUp).Offset(1) 
    
    
        'Ask for temperature workbook name.
        sTemperature = Application.GetOpenFilename("Log Files (*.txt), *.txt")
        If sTemperature = "False" Then Exit Function
        
        Application.ScreenUpdating = False
        
        Workbooks.Open sTemperature, , , 4, , , , , ";"
        Set wbTemperature = ActiveWorkbook
        
        ActiveSheet.Cells(1, 1).CurrentRegion.Copy destCell.Cells(1, 1)
        wbTemperature.Close False
        wbMaster.Activate
        wsImport.Select
        Application.GoTo destCell.Range("A9")
        
        Application.ScreenUpdating = True
    Thanks

  6. #6
    Hello,

    is there a way to ignore the first row like the header? from the csv file
    because it always create the header again

    Dim wbMaster As Workbook
        Dim wbTemperature As Workbook
        Dim sTemperature As String
        Dim wsImport As Worksheet
        Dim destCell As Range
    
    
        Set wbMaster = ThisWorkbook
        Set wsImport = wbMaster.Worksheets("sheetlist2")
        Set destCell = Worksheets("sheetlist2").Cells(Rows.Count, "A").End(xlUp).Offset(1) 
    
    
        'Ask for temperature workbook name.
        sTemperature = Application.GetOpenFilename("Log Files (*.txt), *.txt")
        If sTemperature = "False" Then Exit Function
        
        Application.ScreenUpdating = False
        
        Workbooks.Open sTemperature, , , 4, , , , , ";"
        Set wbTemperature = ActiveWorkbook
        
        ActiveSheet.Cells(1, 1).CurrentRegion.Copy destCell.Cells(1, 1)
        wbTemperature.Close False
        wbMaster.Activate
        wsImport.Select
        Application.GoTo destCell.Range("A9")
        
        Application.ScreenUpdating = True
    Thank you

  7. #7
    Hello,

    i found some useful codes on the net
    and seems i can ignore the header
    but i can't get it on the A9 row

    any help please?

     Dim txtFileName As Variant
        Dim destCell As Range
        
        Set destCell = Worksheets("TestingImport").Cells(Rows.Count, "A").End(xlUp).Offset(1) 
        
        txtFileName = Application.GetOpenFilename(FileFilter:="TXT Files (*.txt),*.txt", Title:="Select a TXT File", MultiSelect:=False)
        If txtFileName= False Then Exit Sub
        
        With destCell.Parent.QueryTables.Add(Connection:="TEXT;" & txtFileName, Destination:=destCell.Cells(1, 1))
            .TextFileStartRow = 2 'this leaves the header away
            .TextFileParseType = xlDelimited
            .TextFileCommaDelimiter = True
            .Refresh BackgroundQuery:=False
        End With
        destCell.Parent.QueryTables(1).Delete
    my old code i can add -1 but above code won't let me

    'old code
    Set destCell = Worksheets("sheetlist2").Cells(Rows.Count, "A").End(xlUp).Offset(-1)
    is it also possible when we import from a txt/csv to create automated id numbers?
    like 1,2,3,4,5 on column R9
    because i can't find anything how to make that happen.

    Thanks
    Last edited by GhostofDoom; 12-17-2019 at 05:44 AM.

  8. #8
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,784
    something like this?:
    Dim txtFileName As Variant
    Dim destCell As Range
    Dim qt
    Set destCell = Worksheets("TestingImport").Range("A9")
        
    txtFileName = Application.GetOpenFilename(FileFilter:="TXT Files (*.txt),*.txt", Title:="Select a TXT File", MultiSelect:=False)
    If txtFileName = False Then Exit Sub
        
    Set qt = destCell.Parent.QueryTables.Add(Connection:="TEXT;" & txtFileName, Destination:=destCell.Cells(1, 1))
    With qt
      .TextFileStartRow = 2    'this leaves the header away
      .TextFileParseType = xlDelimited
      .TextFileCommaDelimiter = True
      .Refresh BackgroundQuery:=False
      With Intersect(.ResultRange.EntireRow, .Parent.Range("R:R"))
        .Cells(1) = 1
        .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=1, Trend:=False
      End With
    End With
    destCell.Parent.QueryTables(1).Delete
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  9. #9
    Hello p45cal ,

    the data isn't adding to my column
    the first row is okay then it just past beside them not over the columns
    and no
    automated id numbers has been created stay 00



    Attached Images Attached Images

  10. #10
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,784
    try replacing:
      .TextFileCommaDelimiter = True
    with:
      .TextFileOtherDelimiter = ";"
    (or just add that line before .refresh.

    Is column R blank where you're wanting the id numbers? If not you can delete them first with a ClearContents:
    Dim txtFileName As Variant
    Dim destCell As Range
    Dim qt
    Set destCell = Worksheets("TestingImport").Cells(Rows.Count, "A").End(xlUp).Offset(1)
    If destCell.Row < 9 Then Set destCell = Worksheets("TestingImport").Range("A9")
        
    txtFileName = Application.GetOpenFilename(FileFilter:="TXT Files (*.txt),*.txt", Title:="Select a TXT File", MultiSelect:=False)
    If txtFileName = False Then Exit Sub
        
    Set qt = destCell.Parent.QueryTables.Add(Connection:="TEXT;" & txtFileName, Destination:=destCell.Cells(1, 1))
    With qt
      .TextFileStartRow = 2    'this leaves the header away
      .TextFileParseType = xlDelimited
      .TextFileCommaDelimiter = True 'you might not need this line at all - it might even be better to make it False
      .TextFileOtherDelimiter = ";"    'add or replace line above
      .RefreshStyle = xlOverwriteCells    '<<changed/added
      .Refresh BackgroundQuery:=False
      With Intersect(.ResultRange.EntireRow, .Parent.Range("R:R"))
        .ClearContents    '<<add
        .Cells(1) = 1
        .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=1, Trend:=False
      End With
    End With
    destCell.Parent.QueryTables(1).Delete
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  11. #11
    awesome

    but still don't get at row A9
    as you can see i created a table
    so it suppose to be in that table so after i add more it just expand

    and thanks id numbers working

    one more thing,

    how can i when making a new column will be A
    its empty because no data has CSV for that column
    how can i add some text when import it to fill A with for example running

    thanks
    Attached Images Attached Images

  12. #12
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,784
    I don't really understand.
    The two lines:
    Set destCell = Worksheets("TestingImport").Cells(Rows.Count, "A").End(xlUp).Offset(1)
    If destCell.Row < 9 Then Set destCell = Worksheets("TestingImport").Range("A9")
    depend on something being in column A each time, if there's always something in ALL rows in column B after the import you can change those two lines to:
    Set destCell = Worksheets("TestingImport").Cells(Rows.Count, "B").End(xlUp).Offset(1, -1)
    If destCell.Row < 9 Then Set destCell = Worksheets("TestingImport").Range("A9")
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  13. #13
    no worries P45cal,

    okay i have added a new column so we have A again and we starting at column B from CSV file
    but column A is empty

    so my question will be
    how can i add in column A the string RUNNING
    when we import the CSV so the column A will not be empty anymore

    edit:
    cool i have the first part working

  14. #14
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,784
    Add
      Intersect(.ResultRange.EntireRow, .Parent.Range("A:A")).Value = "RUNNING"
    straight after:
    .Refresh BackgroundQuery:=False
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  15. #15
    awesome
    works like a charm

    but still the first row won't work
    when i try again it starts again under my table

    so i get always 1 empty line
    just under the black line of my table instead inside of it


    here my sheet
    sorry can't add my CSV file for privacy reasons
    Attached Files Attached Files
    Last edited by GhostofDoom; 12-17-2019 at 12:02 PM.

  16. #16
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,784
    A querytable can't overlap an Excel Table (listobject) and an empty table has at least two rows: header and empty data row.
    So all we can do is let it put the querytable where it wants to, then delete the querytable then extend the Excel Table, then delete all blank rows from the table:
    Sub Append_CSV_File()    ''working from vbaexpress.com
    Dim txtFileName As Variant
    Dim destCell As Range
    Dim qt
    Set destCell = Worksheets("TestingImport").Cells(Rows.Count, "B").End(xlUp).Offset(1)
    If destCell.Row < 9 Then Set destCell = Worksheets("TestingImport").Range("B9")
    
    txtFileName = Application.GetOpenFilename(FileFilter:="TXT Files (*.txt),*.txt", Title:="Select a TXT File", MultiSelect:=False)
    If txtFileName = False Then Exit Sub
        
    Set qt = destCell.Parent.QueryTables.Add(Connection:="TEXT;" & txtFileName, Destination:=destCell.Cells(1, 1))
    With qt
      .TextFileStartRow = 2    'this leaves the header away
      .TextFileParseType = xlDelimited
      .TextFileCommaDelimiter = False    'you might not need this line at all - it might even be better to make it False
      .TextFileOtherDelimiter = Empty
      .TextFileSemicolonDelimiter = True
      .RefreshStyle = xlOverwriteCells    '<<changed/added
      .Refresh BackgroundQuery:=False
      With Intersect(.ResultRange.EntireRow, .Parent.Range("S:S"))
        .ClearContents    '<<add
        .Cells(1) = 1
        .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=1, Trend:=False
      End With
      Intersect(.ResultRange.EntireRow, .Parent.Range("A:A")).Value = "running"
      lr = .ResultRange.Rows(.ResultRange.Rows.Count).Row
      .WorkbookConnection.Delete
      .Delete
    End With
    With Worksheets("TestingImport").Range("B9").ListObject
      Set TL = .Range.Cells(1)
      lc = TL.Column + .ListColumns.Count - 1
      .Resize Range(TL, .Parent.Cells(lr, lc))
      'delete all blank rows in the table:
      For i = .ListRows.Count To 1 Step -1
        With .ListRows(i)
          If Application.CountA(.Range) = 0 Then .Delete
        End With
      Next i
    End With
    End Sub
    The attached has the code in a separate module rather than in the ThisWorkbook code-module.
    Attached Files Attached Files
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  17. #17
    Hello p45cal,

    ooh sorry i didn't really know about that
    thanks for telling me.

    wow cool your sheet is working awesome
    but when i copy your code i get an error and then by TL ect...

    Thank for the wonderful help


    edit:

    Found the problem,
    it seems i can't use the
    Option Explicit

    in the module

    Thanks p45cal for the awesome help
    you are great
    Attached Images Attached Images
    Last edited by GhostofDoom; 12-17-2019 at 09:09 PM.

  18. #18
    p45cal, i see when we import from a new file
    we get the same ID numbers ?

    any solution for that please

  19. #19
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,784
    Quote Originally Posted by GhostofDoom View Post
    p45cal, i see when we import from a new file
    we get the same ID numbers ?

    any solution for that please
    Change:
    .Cells(1) = 1
    to:
    .Cells(1) = .Cells(1).Offset(-1).Value + 1
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  20. #20
    hello p45cal,

    awesome works like a charm

    thanks alot

    edit:

    one more question, how can i ignore when the row is not empty
    because with this code

      Intersect(.ResultRange.EntireRow, .Parent.Range("Q:Q")).Value = "x"
    it overwrite any row if it contains already something from our csv file
    we have the Q column v in the fields
    i want to ignore that and only add x if empty


    Thank you.
    Last edited by GhostofDoom; 12-18-2019 at 07:53 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •