MS Excel - externé údaje z *.CSV

"Rady nielen z Porady" - Tipy a triky, "how-to ...", nápady a riešenia k aplikáciam MS Office
pre všetky verzie až po MS Office 2010

MS Excel - externé údaje z *.CSV

Poslaťod Milan25 » 27.10.2015, 11:50

zdravím, viete mi niekto poradiť ako si zmeniť zdroje externých údajov pre MS Excel z formátu CSV.
Problém je v tom, že sa zmenilo úložisko CSV súborov na iný server a nechcem robiť všetky importy do Excelu nanovo.
Existuje dáka možnosť, kde by som len prepísal cestu k zdroju a import údajov prebehne automaticky, bez nového nastavovania?

vďaka
Milan25
 
Príspevky: 54
Registrovaný: 30.07.2010, 10:28
Udelené poďakovania: 2 krát
Prijaté poďakovania: 0 krát

Re: MS Excel - externé údaje z *.CSV

Poslaťod Palo-admin » 28.10.2015, 11:18

Predpokladám verziu 2007 a vyššie.

1) Potom karta ÚDAJE, voľba PRIPOJENIA
2) V okne Pripojenia k zošitu vybrať pripojenie na príslušný súbor CSV
3) Tlačidlo Vlastnosti, záložka Definícia - zmeniť cestu

P.
Nemáte oprávnenie prezerať súbory priložené v tomto príspevku.
Palo-admin
Administrátor stránky
 
Príspevky: 473
Registrovaný: 18.07.2008, 16:43
Udelené poďakovania: 6 krát
Prijaté poďakovania: 63 krát

Re: MS Excel - externé údaje z *.CSV

Poslaťod Milan25 » 28.10.2015, 11:47

Áno, týmto spôsobom by som to zmenil, ale musím celý import textu vykonať nanovo, teda definovať dátové typy, vynechané stĺpce a podobne.
Je to veľmi prácne a chcel by som len zmeniť cestu na nový server, len v tej ukážke ako píšete mi excel nepovoľuje ručne písať.
Existuje nejaká možnosť ako to spraviť bez nových importov všetkých CSV?
Milan25
 
Príspevky: 54
Registrovaný: 30.07.2010, 10:28
Udelené poďakovania: 2 krát
Prijaté poďakovania: 0 krát

Re: MS Excel - externé údaje z *.CSV

Poslaťod Palo-admin » 28.10.2015, 12:45

Nepoznám pozadie a rozsah požadovanej obnovy.
Ak je však problém napr. zachovanie importu len vybraných dát (či už stĺpcov či riadkov)
asi by som použil inú technológiu.

Pre Excel 2013 existuje jeden príjemný doplnok "Microsoft Power Query",
ktorý umožňuje "prelinkovať" dáta z rôznych formátov a update prístupu k nim je jednoduchý (dá sa i pekne "omakrovať")

Napr cez voľbu EDIT (Launch Editor)

Kód: Vybrať všetko
let
    Source = Csv.Document(File.Contents("C:\tmp\xx\test-csv-subor.csv"),[Delimiter=";",Encoding=1250]),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Nadpis1", Int64.Type}, {"Nadpis2", Int64.Type}, {"Nadpis3", Int64.Type}, {"Nadpis4", Int64.Type}, {"Nadpis5", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Nadpis1", "Nadpis2", "Nadpis4", "Nadpis5"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [Nadpis1] > 30)
in
    #"Filtered Rows"
.

Iný spôsob by bol pre import použiť link cez ODBC s typom importu pre CSV.

Prípadne si pri definovaní importu spustiť záznam makra, ktorý zaznamená podobné makro:
Kód: Vybrať všetko
Sub Import_CSV()

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\tmp\test-csv-subor.csv" _
        , Destination:=Range("$A$1"))
        .CommandType = 0
        .Name = "test-csv-subor"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 9, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range("D8").Select
End Sub

Tam potom stačí upraviť príslušné cesty či vybrané stĺpce a spustiť makro znova.
Palo-admin
Administrátor stránky
 
Príspevky: 473
Registrovaný: 18.07.2008, 16:43
Udelené poďakovania: 6 krát
Prijaté poďakovania: 63 krát

Re: MS Excel - externé údaje z *.CSV

Poslaťod Milan25 » 28.10.2015, 13:30

Jedná sa o verziu 2010 a teda spravil som chybu, že som tie CSV importoval cez klasické externé údaje?
Teraz tie cesty k novému serveru neviem nadefinovať? Po zmene na nové umiestnenie zostávajú aj do budúcna rovnaké, čiže makrá nepotrebujem.
Stačí mi nastaviť len automatické obnovovanie pri otváraní súboru. Chcel som pridať aj screen obrazovky, ale nechce to vziať stránka.
Milan25
 
Príspevky: 54
Registrovaný: 30.07.2010, 10:28
Udelené poďakovania: 2 krát
Prijaté poďakovania: 0 krát

Re: MS Excel - externé údaje z *.CSV

Poslaťod Palo-admin » 28.10.2015, 14:54

Aj s tým sa dá "vykývať"

Cez jednorázovo spustené makro (viď nižšie)
sa dá updatovať cesta k CSV-čkam pre všetky prepojenie vo všetkých listoch akt. zošita

!!!
Treba si vyskúšať na kópii ostrých dát, či náhodou nebolo nastavené iné nastavenie (viď makro vyššie),
napr oddelovače stĺpcov, identifikácia textu ...

Kód: Vybrať všetko
Sub ZmenCestuPreCSV()
    Dim xQT, i, j
         For i = 1 To ActiveWorkbook.Worksheets.Count
            ActiveWorkbook.Worksheets(i).Select
            For j = 1 To ActiveSheet.QueryTables.Count
                Set xQT = ActiveSheet.QueryTables(j)
                With xQT
               
                    If InStr(1, .Connection, "C:\tmp\xx\") <= 0 Then ' ak este nebola zmena
                        .Connection = Replace(.Connection, "C:\tmp\", "C:\tmp\xx\", 1, -1, 1)
                    End If
                   
                    .PreserveFormatting = True
                    .RefreshOnFileOpen = True   'update pri otvoreni
                   
                    .TextFileParseType = xlDelimited
                    .TextFileTextQualifier = xlTextQualifierDoubleQuote
                    .TextFileSemicolonDelimiter = True 'oddelovac bodkociarka
                    .TextFileColumnDataTypes = Array(1, 1, 9, 1, 1) ' ktore stlpce
                   
                    .TextFilePromptOnRefresh = False 'nepytat sa na subor pri update
                    .Refresh
                End With
            Next j
         Next i
End Sub
Nemáte oprávnenie prezerať súbory priložené v tomto príspevku.
Palo-admin
Administrátor stránky
 
Príspevky: 473
Registrovaný: 18.07.2008, 16:43
Udelené poďakovania: 6 krát
Prijaté poďakovania: 63 krát

Re: MS Excel - externé údaje z *.CSV

Poslaťod Milan25 » 28.10.2015, 15:26

ďakujem pekne za ukážku a návod, ale ako si čítam tie kódy, tak v jednej časti spomínate oddeľovač textu a stĺpce na import.
To znamená, že opäť sa dostanem k nastavovaniu zdrojových údajov pre import a to chcem práve obísť, chcem zachovať načítanie externých údajov presne ako sú teraz, len sa zmenil server u nás a nechcem to ručne prerábať, čisto len zameniť názov nového servera v ceste. Celkovo je to nešťastné riešenie, pretože kvôli štandardizácií a premenovaniu servera vznikne kopu aktualizácií.
Milan25
 
Príspevky: 54
Registrovaný: 30.07.2010, 10:28
Udelené poďakovania: 2 krát
Prijaté poďakovania: 0 krát

Re: MS Excel - externé údaje z *.CSV

Poslaťod Palo-admin » 28.10.2015, 15:39

No aj tomu sa dá pomôcť :)

Stačí v makre vymeniť riadok
Kód: Vybrať všetko
               
Set xQT = ActiveSheet.QueryTables(j)
With xQT

za
Kód: Vybrať všetko
 With ActiveSheet.QueryTables(j)

a zrušiť ostatné

"Mea Culpa", neuvedomil som si, že keď to editnem cez premennú, nezachová aktuálne nastavenia.
My sa k tomu postupne dopracujeme :)

P.

Celé nové makro:
Kód: Vybrať všetko
Sub ZmenCestuPreCSV()
    Dim  i, j
         For i = 1 To ActiveWorkbook.Worksheets.Count
            ActiveWorkbook.Worksheets(i).Select
            For j = 1 To ActiveSheet.QueryTables.Count
                With ActiveSheet.QueryTables(j)             
                    If InStr(1, .Connection, "C:\tmp\xx\") <= 0 Then ' ak este nebola zmena
                        .Connection = Replace(.Connection, "C:\tmp\", "C:\tmp\xx\", 1, -1, 1)
                    End If
                    .Refresh
                End With
            Next j
         Next i
End Sub
Palo-admin
Administrátor stránky
 
Príspevky: 473
Registrovaný: 18.07.2008, 16:43
Udelené poďakovania: 6 krát
Prijaté poďakovania: 63 krát

Re: MS Excel - externé údaje z *.CSV

Poslaťod Milan25 » 28.10.2015, 15:58

to som rád, že to ešte nie je stratené :)

Použil som Váš kód, ale excel mi vyhodil chybu "run-time error 1004" - metóda select triedy worksheet zlyhala.

Do časti pre zámenu cesty som zadal nasledovné:
.Connection = Replace(.Connection, "\\scvtabas01\pcdaten\P_AUO_EXPORT.CSV", "\\scvterpsk01\pcdaten\P_AUO_EXPORT.CSV", 1, -1, 1)

vďaka
Milan25
 
Príspevky: 54
Registrovaný: 30.07.2010, 10:28
Udelené poďakovania: 2 krát
Prijaté poďakovania: 0 krát

Re: MS Excel - externé údaje z *.CSV

Poslaťod Palo-admin » 28.10.2015, 16:09

A zdochlo to na tom riadku ".Connection = ..." ?

Nestačí meniť iba názov servera ?
Predpokladám samozrejme, že súbor CSV na serveri je dostupný (treba odskúšať zavolaním napr z Notepadu).

Prípadne by som skúsil cestu na server namapovať, resp celé vyskúšať lokálne.
Palo-admin
Administrátor stránky
 
Príspevky: 473
Registrovaný: 18.07.2008, 16:43
Udelené poďakovania: 6 krát
Prijaté poďakovania: 63 krát

Ďalší

Späť na Word, Excel, PowerPoint, Outlook, Access

Kto je on-line

Užívatelia prezerajúci fórum: Žiadny registrovaný užívateľ nie je prítomný a 1 hosť

cron