Solved

VBA Excel - import csv and sort to different rows

Posted on 2014-09-15
4
943 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

809 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