Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

[EXCEL] MACRO per suddividere file .CSV in diversi fogli XL

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

[EXCEL] MACRO per suddividere file .CSV in diversi fogli XL

Postdi sceriffopeschiera » 31/12/13 12:20

Buongiorno a tutti,
ho bisogno nuovamente del vostro eccezionale supporto.
Ho un grosso file .csv (contenente un numero di righe superiori alle 1.048.576 gestite da Excel). Il mio problema iniziale era convertire il file in questione in .xlsx senza perdere informazioni per strada. Girando un pò sui vari forum ho recuperato questo codice che mi suddivide il file .csv in diversi fogli di max 500.000 righe, salvaguardando l'integrità dei miei dati:

Codice: Seleziona tutto
Public Sub LargeFileImport()
'Dimension Variables
Dim ResultStr As String
Dim FName As Variant
Dim FileNum As Integer
Dim Counter As Double

'Ask User for File's Name
FName = Application.GetOpenFilename( _
FileFilter:="CSV Files (*.csv), *.csv")
If FName = False Then
Exit Sub
End If

'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open FName For Input As #FileNum
'Turn Screen Updating Off
Application.ScreenUpdating = False
'Create A New WorkBook With One Worksheet In It
Workbooks.Add Template:=xlWorksheet
'Set The Counter to 1
Counter = 1
'Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)
'Display Importing Row Number On Status Bar
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & FName
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Store Variable Data Into Active Cell
If Left(ResultStr, 1) = "=" Then
ActiveCell.Value = "'" & ResultStr
Else
ActiveCell.Value = ResultStr
End If

If ActiveCell.Row = 500000 Then
'If On The Last Row Then Add A New Sheet
ActiveWorkbook.Sheets.Add
Else
'If Not The Last Row Then Go One Cell Down
ActiveCell.Offset(1, 0).Select
End If
'Increment the Counter By 1
Counter = Counter + 1
'Start Again At Top Of 'Do While' Statement
Loop
'Close The Open Text File
Close
'Remove Message From Status Bar
Application.StatusBar = False
End Sub


I dati all'interno del file .csv sono organizzati come segue:

[list=]
100","CDU","001","J","5944SP","COO PACK MML EMT","0","0","GBP","20120703","0","1","0","999","E100ABCVAL
100","CDU","001","J","5944SP","COO PACK VIRGIN","0","0","GBP","20121018","0","1","0","999","E100ABCVAL
100","CDU","002","C","31713","GAS, TURBO","0","0","GBP","20090325","0","1","0","999","E100ABCVAL
100","CDU","002","C","36377","WATER HEAD","0","0","GBP","20090325","0","1","0","999","E100ABCVAL
100","CDU","002","C","3086","SEAL","0","0","GBP","20090408","0","1","0","999","E100ABCVAL
[/list]

Ora, il problema nasce dall'esigenza di suddividere i diversi fogli di lavoro, non in base al numero di righe, bensì al variare di un parametro. Più nello specifico, la terza colonna che contiene i valori 001, 002, 003, 004, etc...
Fermo restanto che il file è già ordinato per valori in colonna C, mi serve una macro che suddivida il file .csv in diversi fogli al variare del dato in colonna C. Il risultato dovrebbe essere un singolo file Excel con diversi fogli di lavoro, uno per ciascuno dei diversi valori 00X contenuti in colonna C.

Spero di essermi spiegato in maniera esaustiva.
sceriffopeschiera
Newbie
 
Post: 9
Iscritto il: 02/08/13 13:20

Sponsor
 

Re: [EXCEL] MACRO per suddividere file .CSV in diversi fogli

Postdi Flash30005 » 01/01/14 10:44

Penso si possa fare ma a questo punto sarebbe opportuno creare una macro che prenda direttamente i record dal file csv originale e non dai fogli da te creati.
Per fare questo dovresti inviare un file csv ridotto in numero di righe ma che abbia la stessa struttura del tuo file originale.

ciao
Flash
Win7 + Office 2010 Ita
"Fotografica" al servizio dell'immagine

Ottime opportunità di lavoro (part-time o full-time) con guadagni immediati. Info in MP
Avatar utente
Flash30005
Moderatore
 
Post: 8460
Iscritto il: 27/09/07 11:44
Località: Roma +o-

Re: [EXCEL] MACRO per suddividere file .CSV in diversi fogli

Postdi Anthony47 » 01/01/14 13:56

Questa macro dovrebbe fare quello che chiedi, ovviamente importando direttamente il file prescelto:
Codice: Seleziona tutto
Public Sub FileImportNSplit()
'Dimension Variables
Dim mySplit, FlNZero As Boolean, Old2, NextRow As Long
Dim ResultStr As String
Dim FName As Variant
Dim FileNum As Integer
'
'Ask User for File's Name
FName = Application.GetOpenFilename( _
FileFilter:="CSV Files (*.csv), *.csv")
If FName = False Then
Exit Sub
End If

'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open FName For Input As #FileNum
'Turn Screen Updating Off
Application.ScreenUpdating = False
'
Workbooks.Add Template:=xlWorksheet
 'Import
Do While Not EOF(FileNum)
    Line Input #FileNum, ResultStr
    mySplit = Split(ResultStr, ",")
    If mySplit(2) <> Old2 And FlNZero = True Then
        ActiveWorkbook.Sheets.Add after:=ActiveSheet
    End If
    Old2 = mySplit(2)
    FlNZero = True
    NextRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
    Application.StatusBar = "Importing Row " & _
        NextRow & " for item  " & Old2
    With Cells(NextRow, 1)
        .Value = ResultStr
        .TextToColumns Destination:=Cells(NextRow, 1), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
            Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
            :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
            Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
            ), Array(14, 1), Array(15, 1)), TrailingMinusNumbers:=True
    End With
Loop
'Close The Open Text File
Close
'Remove Message From Status Bar
Application.StatusBar = False
End Sub


Tutti i campi del "TextToColumns" sono stati lasciati di tipo "generale"; volendo impostare formati diversi ricorda che 2=Testo e 5=data in formato AnnoMeseGiorno.

Ciao, fai sapere.
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13885
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [EXCEL] MACRO per suddividere file .CSV in diversi fogli

Postdi sceriffopeschiera » 02/01/14 16:58

Anthony, impeccabile come sempre, la tua macro funziona perfettamente!! :)

Per renderla perfetta alle mie esigenze, se fosse possibile rinominare ciascun foglio con il relativo riferimento (001, 002, 003, 004, etc...). Suppongo che per te sia un'operazione agevole.
Sarei in grado di farlo per i fatti miei se solo la sequenza fosse fissa, ma in realtà può accadere che talvolta uno dei riferimenti sia assente. Ad esempio potrebbe esserci 001, 002, 004, 007, etc... e la volta successiva 001, 002, 003, 006, etc...

Grazie mille in anticipo come sempre!!
sceriffopeschiera
Newbie
 
Post: 9
Iscritto il: 02/08/13 13:20

Re: [EXCEL] MACRO per suddividere file .CSV in diversi fogli

Postdi sceriffopeschiera » 02/01/14 17:29

Anthony,

scusa un ulteriore dubbio. La tua macro dovrebbe anche organizzare il contenuto in colonne? Ti chiedo perchè leggendo la macro con più attenzione, suppongo che questo passaggio si riferisca proprio a ciò:

Codice: Seleziona tutto
.TextToColumns Destination:=Cells(NextRow, 1), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
            Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
            :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
            Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
            ), Array(14, 1), Array(15, 1)), TrailingMinusNumbers:=True


In realtà, a macro ultimata, ciascun foglio contiene i dati nella forma originale del file .CSV, e cioè con i relativi separatori ",".
sceriffopeschiera
Newbie
 
Post: 9
Iscritto il: 02/08/13 13:20

Re: [EXCEL] MACRO per suddividere file .CSV in diversi fogli

Postdi Flash30005 » 02/01/14 17:43

Ho provato la macro proposta da Anthony e separa tutti i valori/stringhe in colonne senza riportare la virgola come dici
Leggendo il post precedente sulla numerazione dei fogli ho provato a fare una modifica e pubblico la macro modificata
Codice: Seleziona tutto
Public Sub FileImportNSplit()
'Dimension Variables
Dim mySplit, FlNZero As Boolean, Old2, NextRow As Long
Dim ResultStr As String
Dim FName As Variant
Dim FileNum As Integer
'
'Ask User for File's Name
FName = Application.GetOpenFilename( _
FileFilter:="CSV Files (*.csv), *.csv")
If FName = False Then
Exit Sub
End If

'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open FName For Input As #FileNum
'Turn Screen Updating Off
Application.ScreenUpdating = False
'
Workbooks.Add Template:=xlWorksheet
 'Import
Do While Not EOF(FileNum)
    Line Input #FileNum, ResultStr
    mySplit = Split(ResultStr, ",")
    If mySplit(2) <> Old2 And FlNZero = True Then
        For I = 1 To Worksheets.Count
        If Sheets(I).Name = "Foglio1" Then Sheets("Foglio1").Name = "001"
            If Sheets(I).Name = mySplit(2) Then
                Sheets(I).Select
                GoTo saltaSh

            '  ActiveWorkbook.Sheets.Add After:=ActiveSheet
            End If
        Next I
        NomeF = Mid(mySplit(2), 2, 3)
        ActiveWorkbook.Worksheets.Add.Name = NomeF
        Sheets(NomeF).Move After:=Worksheets(Worksheets.Count)
        End If
saltaSh:
    Old2 = mySplit(2)
    FlNZero = True
    NextRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
    Application.StatusBar = "Importing Row " & _
        NextRow & " for item  " & Old2
    With Cells(NextRow, 1)
        .Value = ResultStr
        .TextToColumns Destination:=Cells(NextRow, 1), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
            Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
            :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
            Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
            ), Array(14, 1), Array(15, 1)), TrailingMinusNumbers:=True
    End With
Loop
'Close The Open Text File
Close
'Remove Message From Status Bar
Application.StatusBar = False
End Sub



Fai sapere
ciao
Flash
Win7 + Office 2010 Ita
"Fotografica" al servizio dell'immagine

Ottime opportunità di lavoro (part-time o full-time) con guadagni immediati. Info in MP
Avatar utente
Flash30005
Moderatore
 
Post: 8460
Iscritto il: 27/09/07 11:44
Località: Roma +o-

Re: [EXCEL] MACRO per suddividere file .CSV in diversi fogli

Postdi Anthony47 » 02/01/14 19:42

Si, la macro dovrebbe anche fare il TestoInColonna con l' istruzione che hai individuato.
Sei sicuro che nei tuoi dati reali il separatore di campo sia "virgola"? Il problema ce l' hai saltuariamente o sistematicamente?
Per assegnare il nome al foglio, nella mia macro basterebbe aggiungere una riga in queste due posizioni:
Codice: Seleziona tutto
    If mySplit(2) <> Old2 And FlNZero = True Then
        ActiveSheet.Name = Replace(Old2, """", "")        '<<< AGGIUNGERE
        ActiveWorkbook.Sheets.Add after:=ActiveSheet
    End If

Codice: Seleziona tutto
ActiveSheet.Name = Replace(Old2, """", "")        '<<< AGGIUNGERE
Application.StatusBar = False
End Sub

Immagino che la macro di Flash (vedi sopra) sia altrettanto efficace.

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13885
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [EXCEL] MACRO per suddividere file .CSV in diversi fogli

Postdi sceriffopeschiera » 03/01/14 10:17

Anthony, ho aggiunto la modifica da te suggerita per rinominare i fogli e funziona, grazie! ;)

Flash, la tua Macro invece mi si blocca dopo aver generato e rinominato correttamente il foglio "001" e con il secondo foglio che contiene i dati ma è erroneamente rinominato come ""00". Il debug mi evidenzia questo punto:

Codice: Seleziona tutto
ActiveWorkbook.Worksheets.Add.Name = NomeF


Riguardo alla formattazione dei dati, entrambe la Macro non organizzano i dati del mio file in colonne.
L'errore è sistematico, nel senso che ogni foglio si presenta nello stesso formato del file .csv, con ancora tutti i separatori ",".

Ho notato che la prima riga resta sempre vuota (i dati vengono riportati a partire dalla riga 2 in ciascuno dei fogli). Non ho idea se questo fosse previsto.
Ho provato ad aggiungere (so bene che non è una soluzione particolarmente elegante :D ) un comando per suddividere i dati in colonne a valle del codice, come segue:

Codice: Seleziona tutto
    Sheets("001").Select
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
        ), Array(14, 1), Array(15, 1)), TrailingMinusNumbers:=True


Così funziona, ma, misteriosamente, l'ultima riga resta con le tabulazioni (lo stesso accade sugli altri fogli diversi da 001). Se invece cancello la prima riga prima della riorganizzazione per colonne:

Codice: Seleziona tutto
Sheets("001").Select
rows("1:1").Delete    'AGGIUNTO
    Columns("A:A").Select


Tutto viene convertito in colonne senza problemi. Forse questo può agevolarvi nel capire da dove nasce il problema.
sceriffopeschiera
Newbie
 
Post: 9
Iscritto il: 02/08/13 13:20

Re: [EXCEL] MACRO per suddividere file .CSV in diversi fogli

Postdi Anthony47 » 03/01/14 16:00

I dati che avevi pubblicato non avevano il doppio apice iniziale sul primo campo:
100","CDU","001","J","5944SP","COO PACK MML EMT","0","0","GBP","20120703","0","1","0","999","E100ABCVAL
100","CDU","001","J","5944SP","COO PACK VIRGIN","0","0","GBP","20121018","0","1","0","999","E100ABCVAL

Potrebbe esserci un comportamento imprevedibile legato a questa assenza; io ho provato con file di test sia privi di questo carattere iniziale sia avendolo aggiunto, e il comportamento e' analogo (i dati vengono spalmati in colonna all' esecuzione della macro).
Provato sia su XL2010 che XL2003.
Tu su che versione lavori?

Mi confermi anche che hai copiato la macro dal mio messaggio e incollata nel vba (cioe' senza risciverla)?

I dati cominciano su riga 2 perche' in genere riga1 viene usata per le intestazioni.

Io direi che, vista l' impossibilita' per me di ricreare il problema, va bene aggiungere le tue istruzioni al loop.

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13885
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [EXCEL] MACRO per suddividere file .CSV in diversi fogli

Postdi sceriffopeschiera » 04/01/14 03:03

Dunque io uso XL 2007 e ti confermo sia che non c'è nessun doppio apice iniziale e che ho copiato ed incollato il tuo codice senza riscriverlo (ci avrei messo un'ora a riscrivermelo tutto, non sono così' matto :D ).
Fermo restando che permane il mistero, procedo con la mia soluzione "alla buona".
Grazie mille per il supporto Anthony.
Alla prossima
sceriffopeschiera
Newbie
 
Post: 9
Iscritto il: 02/08/13 13:20

Re: [EXCEL] MACRO per suddividere file .CSV in diversi fogli

Postdi Anthony47 » 05/01/14 01:19

Come ultima prova potresti provare con un csv che contiene anche le prime "virgolette", cioe':
Codice: Seleziona tutto
"100","CDU", etc etc
"100","CDU","001","J",etc etc
etc etc

Si tratta infatti di una esportazione errata, visto che le virgolette dovrebbero (opzionalmente) racchiudere i campi, quindi e' sbagliato avere un file che comincia con 100", dovrebbe cominciare con "100".

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13885
Iscritto il: 21/03/06 16:03
Località: Ivrea


Torna a Applicazioni Office Windows


Topic correlati a "[EXCEL] MACRO per suddividere file .CSV in diversi fogli XL":


Chi c’è in linea

Visitano il forum: Nessuno e 6 ospiti