Solved

VBA Excel - import csv and sort to different rows

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

770 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