Solved

VBA Excel - import csv and sort to different rows

Posted on 2014-09-15
4
961 Views
Last Modified: 2014-09-15
hi expert,
the VBA code below import a csv into the active worksheet with delimiter ";" so far so good.
I need your help to implement 2-3 features

1. The name of the existing worksheet is always "IMPORT" not should use the active worksheet function

2. The CSV data should sort from the column J  "Action" which has 3 Values (new,delete,we)
insert "new" from A2 until A150
insert "delete" from A151 until A185
insert "we" from A186 until A220

3. Not so important : It should clear the worksheet "Import" before from row A2. A1 has the header

Thanks in advance for your answers.
Best Regards
Mandy

Sub Datei_Importieren()
  Dim strFileName As String, arrDaten, arrTmp, lngR As Long, lngLast As Long
  Const cstrDelim As String = ";" 'Delimiter
  
  With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Title = "Choose Filename"
    .InitialFileName = "c:\temp\*.csv"  'Pfad anpassen
    If .Show = -1 Then
      strFileName = .SelectedItems(1)
    End If
  End With
  
  If strFileName <> "" Then
    Application.ScreenUpdating = False
    Open strFileName For Input As #1
    arrDaten = Split(Input(LOF(1), 1), vbCrLf)
    Close #1
    For lngR = 1 To UBound(arrDaten)
      arrTmp = Split(arrDaten(lngR), cstrDelim)
      If UBound(arrTmp) > -1 Then
        With ActiveSheet
          lngLast = .Cells(Rows.Count, 1).End(xlUp).Row + 1
          lngLast = Application.Max(lngLast, 10)
          .Cells(lngLast, 1).Resize(, UBound(arrTmp) + 1) _
            = Application.Transpose(Application.Transpose(arrTmp))
        End With
      End If
    Next lngR
  End If
  
End Sub

Open in new window

0
Comment
Question by:Mandy_
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
Kimputer earned 500 total points
ID: 40323043
Updated code for point 1 and 3 (please make sure you have a sheet with the name "IMPORT", as I didn't insert error checking code for now)

Sub Datei_Importieren()
  Dim strFileName As String, arrDaten, arrTmp, lngR As Long, lngLast As Long
  Const cstrDelim As String = ";" 'Delimiter
  
  Dim importsheet As Worksheet
  Set importsheet = ActiveWorkbook.Sheets("IMPORT")
  
 
  Application.ScreenUpdating = False
  For i = importsheet.UsedRange.Rows.Count To 2 Step -1
    importsheet.Cells(i, 1).EntireRow.Delete
  Next
  Application.ScreenUpdating = True

 
  With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Title = "Choose Filename"
    .InitialFileName = "c:\temp\*.csv"  'Pfad anpassen
    If .Show = -1 Then
      strFileName = .SelectedItems(1)
    End If
  End With
  
  If strFileName <> "" Then
    Application.ScreenUpdating = False
    Open strFileName For Input As #1
    arrDaten = Split(Input(LOF(1), 1), vbCrLf)
    Close #1
    For lngR = 1 To UBound(arrDaten)
      arrTmp = Split(arrDaten(lngR), cstrDelim)
      If UBound(arrTmp) > -1 Then
        With importsheet
          lngLast = .Cells(Rows.Count, 1).End(xlUp).Row + 1
          lngLast = Application.Max(lngLast, 2)
          .Cells(lngLast, 1).Resize(, UBound(arrTmp) + 1) _
            = Application.Transpose(Application.Transpose(arrTmp))
        End With
      End If
    Next lngR
  End If
  
End Sub

Open in new window


Point 2 is a bit of a mystery to me right now. Maybe you can send a sample csv file? And then you talk me through it step by step? It sounds a bit illogical that those numbers are always fixed that you gave earlier (2, 151, 186, 220).
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 40323078
Re 2: From earlier communications:
Rows with specific action values (New, Delete, WE) should be inserted at different locations, e.g. all "Delete"  occupy rows 151 to 185.
0
 
LVL 2

Author Comment

by:Mandy_
ID: 40323109
Hi kimputer,

Thank you so much for help. The CSV you can find as attachment.
There are 3 datasets included.  NEW, DELETE, WE  at the column Aktion.
At import process they should be sort
all Aktion, New        from Row 2
all Aktion, delete     from row 151
all Aktion, We          from row 186

Needs to be insert from row 2, 151 and 186

the number of datasets are variable. Sometimes "New" from 2 to 20 or Delete from 151 to 160 only.
The data will process in other worksheet depends on the data in different rows.
ee-example2.csv
0
 
LVL 2

Author Closing Comment

by:Mandy_
ID: 40324688
appreciate fpr your assistence. For the missing part i will open a new question
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question