Macro Excel modifica CSV

Hai un problema con Excel?

Nuova discussione

Macro Excel modifica CSV
di CeciliaG (utente non iscritto) data: 02/02/2015 10:09:41
Buongiorno agli utenti del forum,

mi presento, Cecilia, 24 anni da Modena. Lavoro come segretaria presso una ditta di import/export e sono qui proprio a causa del mio lavoro. Mi è stato chiesto di creare una Macro Excel (semplicissima a sentire il parere di chi me la ha commissionata), peccato che dopo un week end di lavoro non abbia ancora combinato nulla. Non avendo mai utilizzato tale programmazione non so proprio da dove cominciare ad essere sincera. Chiedo perciò l'aiuto di chi più esperto di me sul forum.

A partire dal file CSV disponibile a questo link (scusate gli spazi ma non mi fa inserire il link)

w w w . wetransfer . com/downloads/91a873843b15b0e5c681554fe7db97cc20150202085910/559e6db4ba68a337206695856596180120150202085910/bebce8

Ecco la descrizione di cosa dovrebbe fare la macro:

-separare i dati in colonne utilizzando il separatore virgola. Il numero delle righe del file non è conosciuto a priori e pertanto la macro deve riconoscere quante righe ci sono in modo automatico.
-a partire dalla colonna B e per tutte le colonne, sostituire il numero "1" al valore "true" e il numero "0" al valore "false".
-a partire dalla colonna B e per tutte le colonne: colonna per colonna si scorrono le righe. Finche si incontra "nan" si deve sostituire con una casella vuota. Quando in una colonna si incontra un valore valido tale valore viene copiato nelle celle sottostanti al posto del "nan" fino a che non si incontra un altro valore valido. A questo punto questo ultimo valore valido verrà copiato nell celle sottostanti al posto del "nan"....e cosi via.

Per fare un esempio:

la colonna B alla fine della macro deve risultare cosi:

B2 vuoto (poichè nan e prima di tale nan non erano presenti validi valori)
B3 fino a B7 = 1.3 (cioè 1.3 e stato copiato in tutte le sottostanti nan)
B8 fino a B16 = 1.2 (cioè 1.2 e stato copiato in tutte le sottostanti nan)

la colonna D alla fine della macro deve risultare cosi:

B2 fino a B3 = 89.4 (cioè 89.4 e stato copiato in tutte le sottostanti nan)
B4 = 89.3
B5 = 89.4
B6 = 89.2
B7 fino a B8 = 89.5 (cioè 89.5 e stato copiato in tutte le sottostanti nan)

Grazie in anticipo a chi saprà aiutarmi.
di lepat (utente non iscritto) data: 02/02/2015 10:20:40
allega il file, non riesco a scaricarlo
di CeciliaG (utente non iscritto) data: 02/02/2015 11:27:35
In allegato i file .csv e .zip.

Grazie

di lepat (utente non iscritto) data: 03/02/2015 09:17:47
una cosa del genere va bene tanto per cominciare ?

Sub ImportCSVFileComma()
    filepath = "C:UsersandreaDesktop	est.csv"
    linenumber = 0
    elementnumber = 0
    Open filepath For Input As #1
        Do While Not EOF(1)
            linenumber = linenumber + 1
            Line Input #1, line
            arrayOfElements = Split(line, ",")
            elementnumber = 0
            For Each element In arrayOfElements
                elementnumber = elementnumber + 1
                Cells(linenumber, elementnumber).Value = element
            Next
        Loop
    Close #1
    Stop
LR = Cells(Rows.Count, "A").End(xlUp).Row
Range("B2:E" & LR).Replace What:="nan", Replacement:="", SearchOrder:=xlByColumns, MatchCase:=True
Range("B2:E" & LR).Replace What:=False, Replacement:=0, SearchOrder:=xlByColumns, MatchCase:=True
Range("B2:E" & LR).Replace What:=True, Replacement:=1, SearchOrder:=xlByColumns, MatchCase:=True

End Sub

di lepat (utente non iscritto) data: 03/02/2015 09:27:00
se va bene allegami un file col risultato finale desiderato
di CeciliaG (utente non iscritto) data: 03/02/2015 09:38:12
Inanzitutto grazie per la risposta...

la macro suddivide i dati in colonne nel modo corretto...segnalo già che nell esempio che io ho creato ci sono 5 colonne..ma in futuro saranno un numero non conosciuto a priori.

Al posto di "1" o "0" in sostituzione a "true" e "false" ottengo una intera colonna di "FALSO".
I "nan" restano dove sono e non vengono cancellati, anche se guardando la macro mi pare di capire che l'intento sia di lasciare per ora una casella vuota al posto dei "nan".
Almeno questo è quello che ho capito sforzandomi di decifrare il codice :-D.
di CeciliaG (utente non iscritto) data: 03/02/2015 09:46:08
Per semplicità allego due file che dal numero di righe ridotto:

-test_in.cvs che è il file in ingresso alla macro
-test_out.csv il formato che desidero ottenere dopo l'elaborazione.
di lepat (utente non iscritto) data: 03/02/2015 10:03:39
elimina la riga stop dalla macro precedente e dimmi se siamo sulla buona strada
di CeciliaG (utente non iscritto) data: 03/02/2015 10:13:25
Provato....sembra ottimo per ora.
Nel senso che ha:

-diviso correttamente le colonne
-sostituito i true e false con 1 e 0
-eliminato i nan da ogni cella

Ora resta solo per ogni colonna dalla B in poi lasciare le righe vuote fino a che non si incontra un valore valido. Trascinare poi tale valore valido fino al successivo valore valido in modo che riempia le celle delle righe sottostanti vuote. Incontrato il nuovo valore valido sarà lui a rimepire le righe delle celle sottostanti.

Per intenderci...a macro eseguita ecco cosa ottengo, presa per esempio la colonna B la prima riga resta vuota, il valore 1.3 viene copiato fino alla cella prima di 1.2. Il valore 1.2 viene copiato fino alla cella prima di 1.3...e cosi via. Per tutte le colonne successive che nell esempio erano solo 4 ma che in realtà sono variabili in numero ogni volta.

time stato1 stato2 stato3 stato4
27-Jan-15 10:03:07 PM CET 0 89.4
27-Jan-15 10:03:16 PM CET 1.3 0
27-Jan-15 10:06:12 PM CET 0 89.3
27-Jan-15 10:07:12 PM CET 0 89.4
27-Jan-15 10:11:11 PM CET 0 89.2
27-Jan-15 10:12:13 PM CET 0 89.5
27-Jan-15 10:14:19 PM CET 1.2 0
27-Jan-15 10:15:15 PM CET 0 89.3
27-Jan-15 10:16:12 PM CET 0 89.4
27-Jan-15 10:19:16 PM CET 0 89.6
27-Jan-15 10:20:16 PM CET 0 89.9
27-Jan-15 10:21:18 PM CET 0 89.7
27-Jan-15 10:22:16 PM CET 0 89.9
27-Jan-15 10:23:17 PM CET 0 89.8
27-Jan-15 10:25:17 PM CET 0 89.5
27-Jan-15 10:25:22 PM CET 1.3 0
27-Jan-15 10:28:18 PM CET 0 89.3
27-Jan-15 10:29:18 PM CET 0 89.1 29.5
27-Jan-15 10:31:21 PM CET 0 89.3
27-Jan-15 10:31:25 PM CET 1.2 0
27-Jan-15 10:35:20 PM CET 0 89.4
27-Jan-15 10:38:25 PM CET 0 89.7
27-Jan-15 10:42:23 PM CET 0 89.9
27-Jan-15 10:47:25 PM CET 0 89.7
27-Jan-15 10:53:27 PM CET 0 89.5
27-Jan-15 10:55:30 PM CET 1 89.4
27-Jan-15 11:00:31 PM CET 0 89.6
27-Jan-15 11:04:35 PM CET 0 89.7
27-Jan-15 11:08:36 PM CET 0 89.6
27-Jan-15 11:09:36 PM CET 0 89.4
27-Jan-15 11:11:35 PM CET 0 89.8
27-Jan-15 11:16:37 PM CET 0 89.6
27-Jan-15 11:17:37 PM CET 0 89.5
27-Jan-15 11:20:39 PM CET 0 89.3
27-Jan-15 11:22:39 PM CET 0 89.5
27-Jan-15 11:23:39 PM CET 0 89.6
27-Jan-15 11:26:42 PM CET 0 89.9
27-Jan-15 11:26:51 PM CET 1.3 0

di lepat (utente non iscritto) data: 03/02/2015 10:25:40
prova questa

Sub ImportCSVFileComma()
    filepath = "C:UsersandreaDesktop	est_in.csv"
    linenumber = 0
    elementnumber = 0
    Open filepath For Input As #1
        Do While Not EOF(1)
            linenumber = linenumber + 1
            Line Input #1, line
            arrayOfElements = Split(line, ",")
            elementnumber = 0
            For Each element In arrayOfElements
                elementnumber = elementnumber + 1
                Cells(linenumber, elementnumber).Value = element
            Next
        Loop
    Close #1
    'Stop
LR = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range("B2:E" & LR)
Rng.Replace What:="nan", Replacement:="", SearchOrder:=xlByColumns, MatchCase:=True
Rng.Replace What:=False, Replacement:=0, SearchOrder:=xlByColumns, MatchCase:=True
Rng.Replace What:=True, Replacement:=1, SearchOrder:=xlByColumns, MatchCase:=True

Set Rng = Range("B3:E" & LR)
Rng.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
Rng.Value = Rng.Value
Cells(1, 1).Select
End Sub

di CeciliaG (utente non iscritto) data: 03/02/2015 10:39:27
Direi quasi perfetto!!!

imprecisone si verifca nella colonna E dove lascia correttamente la prima cella vuota...poi dalla seconda inizia a riempire erroneamente con degli zeri fino a che non incontra il vero primo valore valido...in pratica quegli zeri non dovrebbero esserci.

Leggendo nel codice non ho capito dove inizializza quello zero!
Inoltre altro dettaglio, se ben capisco il codice, la macro funziona per 5 colonne...non per un numero casuale.

Cmq e quasi il risultato desiderato!
di CeciliaG (utente non iscritto) data: 03/02/2015 11:58:32
ho fatto delle prove ulteriori cambiando il file di ingresso...

l'imprecisione è che in una colonna finchè non trova valori validi riempie le righe con il valore "0" ..tranne la riga 2 che lascia correttamente vuota. Esempio qui sotto:

time stato1 stato2 stato3 stato4
27-Jan-15 10:03:07 PM CET 0
27-Jan-15 10:03:16 PM CET 0 0 0 0
27-Jan-15 10:06:12 PM CET 0 0 89.3 0
27-Jan-15 10:07:12 PM CET 0 0 89.4 0
27-Jan-15 10:11:11 PM CET 0 0 89.2 0
27-Jan-15 10:12:13 PM CET 0 0 89.5 0
27-Jan-15 10:14:19 PM CET 1.2 0 89.5 0
27-Jan-15 10:15:15 PM CET 1.2 0 89.3 0
27-Jan-15 10:16:12 PM CET 1.2 0 89.4 0
27-Jan-15 10:19:16 PM CET 1.2 0 89.6 0
27-Jan-15 10:20:16 PM CET 1.2 0 89.9 0
27-Jan-15 10:21:18 PM CET 1.2 0 89.7 0
27-Jan-15 10:22:16 PM CET 1.2 0 89.9 0
27-Jan-15 10:23:17 PM CET 1.2 0 89.8 0
27-Jan-15 10:25:17 PM CET 1.2 0 89.5 0
27-Jan-15 10:25:22 PM CET 1.3 0 89.5 0
27-Jan-15 10:28:18 PM CET 1.3 0 89.3 0
27-Jan-15 10:29:18 PM CET 1.3 0 89.1 29.5
27-Jan-15 10:31:21 PM CET 1.3 0 89.3 29.5
27-Jan-15 10:31:25 PM CET 1.2 0 89.3 29.5
27-Jan-15 10:35:20 PM CET 1.2 0 89.4 29.5
27-Jan-15 10:38:25 PM CET 1.2 0 89.7 29.5
27-Jan-15 10:42:23 PM CET 1.2 0 89.9 29.5
27-Jan-15 10:47:25 PM CET 1.2 0 89.7 29.5
27-Jan-15 10:53:27 PM CET 1.2 0 89.5 29.5
27-Jan-15 10:55:30 PM CET 1.2 1 89.4 29.5
27-Jan-15 11:00:31 PM CET 1.2 0 89.6 29.5
27-Jan-15 11:04:35 PM CET 1.2 0 89.7 29.5
27-Jan-15 11:08:36 PM CET 1.2 0 89.6 29.5
27-Jan-15 11:09:36 PM CET 1.2 0 89.4 29.5
27-Jan-15 11:11:35 PM CET 1.2 0 89.8 29.5
27-Jan-15 11:16:37 PM CET 1.2 0 89.6 29.5
27-Jan-15 11:17:37 PM CET 1.2 0 89.5 29.5
27-Jan-15 11:20:39 PM CET 1.2 0 89.3 29.5
27-Jan-15 11:22:39 PM CET 1.2 0 89.5 29.5
27-Jan-15 11:23:39 PM CET 1.2 0 89.6 29.5
27-Jan-15 11:26:42 PM CET 1.2 0 89.9 29.5
27-Jan-15 11:26:51 PM CET 1.3 0 89.9 29.5

di lepat (utente non iscritto) data: 03/02/2015 12:17:43
prova ora

Sub ImportCSVFileComma()
    filepath = "C:UsersandreaDesktop	est_in.csv"
    linenumber = 0
    elementnumber = 0
    Open filepath For Input As #1
        Do While Not EOF(1)
            linenumber = linenumber + 1
            Line Input #1, line
            arrayOfElements = Split(line, ",")
            elementnumber = 0
            For Each element In arrayOfElements
                elementnumber = elementnumber + 1
                Cells(linenumber, elementnumber).Value = element
            Next
        Loop
    Close #1

LR = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range("B2:E" & LR)
Rng.Replace What:="nan", Replacement:="", SearchOrder:=xlByColumns, MatchCase:=True
Rng.Replace What:=False, Replacement:=0, SearchOrder:=xlByColumns, MatchCase:=True
Rng.Replace What:=True, Replacement:=1, SearchOrder:=xlByColumns, MatchCase:=True

Set Rng = Range("B3:E" & LR)
Rng.SpecialCells(xlCellTypeBlanks).Select
'Selection.FormulaR1C1 = "=R[-1]C"
Selection.FormulaR1C1 = "=IF(R[-1]C <> """",R[-1]C,"""")"
Rng.Value = Rng.Value
Cells(1, 1).Select
End Sub

di CeciliaG (utente non iscritto) data: 03/02/2015 12:28:41
Assolutamente perfetto!!!

Ultima cosa poi non romperò piu le scatole...
estendere la procedura non solo fino alla colonna E ma ad un numero casuale di colonne...
tipo replicando l'inidce di riga

LR = Cells(Rows.Count, "A").End(xlUp).Row

ma fatto per le colonne....ci sto provando senza molto successo!

di lepat (utente non iscritto) data: 03/02/2015 12:38:42
prova questa

Sub ImportCSVFileComma()
    filepath = "C:UsersutenteDesktop	est_in.csv"
    linenumber = 0
    elementnumber = 0
    Open filepath For Input As #1
        Do While Not EOF(1)
            linenumber = linenumber + 1
            Line Input #1, line
            arrayOfElements = Split(line, ",")
            elementnumber = 0
            For Each element In arrayOfElements
                elementnumber = elementnumber + 1
                Cells(linenumber, elementnumber).Value = element
            Next
        Loop
    Close #1
Set Rng = ActiveSheet.UsedRange.Offset(1, 1)
Rng.Replace What:="nan", Replacement:="", SearchOrder:=xlByColumns, MatchCase:=True
Rng.Replace What:=False, Replacement:=0, SearchOrder:=xlByColumns, MatchCase:=True
Rng.Replace What:=True, Replacement:=1, SearchOrder:=xlByColumns, MatchCase:=True
Set Rng = Rng.Offset(1, 0) 
Rng.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=IF(R[-1]C <> """",R[-1]C,"""")"
Rng.Value = Rng.Value
Cells(1, 1).Select
End Sub

di CeciliaG (utente non iscritto) data: 03/02/2015 14:55:52
No ora da un errore durante l'esecuzione.

credo sia qualcosa dovuto ai formati perchè apro e chiudo il .cvs di origine e lo salvo anche senza fare modifiche poi la macro si comporta bene.
ma se uso il csv vergine cosi come l'altro software me lo crea ottengo un errore durante l'esecuzione!
di CeciliaG (utente non iscritto) data: 03/02/2015 15:09:42
Per intenderci.....l'ultimo file che mi e stato girato è quello che ti allego...chiamato test_new.csv

su questo compare l'errore!
di lepat (utente non iscritto) data: 03/02/2015 15:58:45
il file ha troppe colonne, supera la capacità di excel
di lepat (utente non iscritto) data: 03/02/2015 16:04:15
non ha troppe colonne, ma c'è qualcosa che non va, forse manca qualche carriage return
di lepat (utente non iscritto) data: 03/02/2015 16:11:58
te ne puoi rendere conto aprendolo con notepad e disattivando l'accapo automatico

di CeciliaG (utente non iscritto) data: 03/02/2015 16:33:51
Ho trovato una soluzione...

ora sembra funzionare.. in pratica ho fatto manualmente l'operazione di selezione celle e tabulazione registrando nel frattempo una macro.
Poi ho preso tale parte del codice e sostituita nell'altro..

Anche se non è molto elegante ho poi manulemente esteso a 100 colonne la tabulazione. Ora funziona e so che se avro file di piu di 100 colonne (dubito) andrò a modificare tale parte.

Grazie mille per l'aiuto!!

Sub ImportCSVFileComma()
 Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, 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), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), _
                Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), _
                Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), _
                Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), Array(46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), _
                Array(51, 1), Array(52, 1), Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), Array(59, 1), Array(60, 1), _
                Array(61, 1), Array(62, 1), Array(63, 1), Array(64, 1), Array(65, 1), Array(66, 1), Array(67, 1), Array(68, 1), Array(69, 1), Array(70, 1), _
                Array(71, 1), Array(72, 1), Array(73, 1), Array(74, 1), Array(75, 1), Array(76, 1), Array(77, 1), Array(78, 1), Array(79, 1), Array(80, 1), _
                Array(81, 1), Array(82, 1), Array(83, 1), Array(84, 1), Array(85, 1), Array(86, 1), Array(87, 1), Array(88, 1), Array(89, 1), Array(90, 1), _
                Array(91, 1), Array(92, 1), Array(93, 1), Array(94, 1), Array(95, 1), Array(96, 1), Array(97, 1), Array(98, 1), Array(99, 1), Array(100, 1)), _
        TrailingMinusNumbers:=True
Set Rng = ActiveSheet.UsedRange.Offset(1, 1)
Rng.Replace What:="nan", Replacement:="", SearchOrder:=xlByColumns, MatchCase:=True
Rng.Replace What:=False, Replacement:=0, SearchOrder:=xlByColumns, MatchCase:=True
Rng.Replace What:=True, Replacement:=1, SearchOrder:=xlByColumns, MatchCase:=True
Set Rng = Rng.Offset(1, 0)
Rng.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=IF(R[-1]C <> """",R[-1]C,"""")"
Rng.Value = Rng.Value
Cells(1, 1).Select
End Sub

di lepat (utente non iscritto) data: 03/02/2015 16:53:20
complimenti, soluzione ingegnosa, puoi semplificarla così

Sub ChangeCSVFileComma()
 Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False
Set Rng = ActiveSheet.UsedRange.Offset(1, 1)
Rng.Replace What:="nan", Replacement:="", SearchOrder:=xlByColumns, MatchCase:=True
Rng.Replace What:=False, Replacement:=0, SearchOrder:=xlByColumns, MatchCase:=True
Rng.Replace What:=True, Replacement:=1, SearchOrder:=xlByColumns, MatchCase:=True
Set Rng = Rng.Offset(1, 0)
Rng.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=IF(R[-1]C <> """",R[-1]C,"""")"
Rng.Value = Rng.Value
Cells(1, 1).Select
End Sub

di lepat (utente non iscritto) data: 03/02/2015 17:01:10
o meglio ancora

Sub ChangeCSVFileComma()
filepath = "C:UsersutenteDesktop	est_new.csv"
Workbooks.OpenText Filename:=filepath, Origin _
        :=65001, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=True, Space:=False, Other:=False, TrailingMinusNumbers:=True
Set Rng = ActiveSheet.UsedRange.Offset(1, 1)
Rng.Replace What:="nan", Replacement:="", SearchOrder:=xlByColumns, MatchCase:=True
Rng.Replace What:=False, Replacement:=0, SearchOrder:=xlByColumns, MatchCase:=True
Rng.Replace What:=True, Replacement:=1, SearchOrder:=xlByColumns, MatchCase:=True
Set Rng = Rng.Offset(1, 0)
Rng.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=IF(R[-1]C <> """",R[-1]C,"""")"
Rng.Value = Rng.Value
Cells(1, 1).Select
End Sub

Vuoi Approfondire?