Keskustelu: Ohjelmointikysymykset: VBA: Excel-tiedoston muuttaminen tektimuotoiseksi

Mamma [02.06.2014 15:26:22]

Heippa!
Minulla on Excel-tiedosto, jonka haluan muuttaa tekstitiedostoksi, jotta saan vietyä sen meidän tietokantaan. Olen muuttanut sen aiemmin ensiksi prn-muotoiseksi ja sen jälkeen txt-muotoiseksi käsin tallentamalla. Tässä on vaan se ongelma, että tuo prn-tiedostomuoto katkaisee tiedoston 240 merkin jälkeen. Eli kun olen sitten vienyt tuon txt-muotoisen tiedoston meidän tietokantaan, olen joutunut viemään loput tiedot käsin suoraan siitä Excel-tiedostosta.
Nyt olen yrittänyt tehdä jonkinlaista Vba-koodia, jolla saisin muutettua tuon Excel-tiedoston tekstimuotoiseksi niin, että kaikki merkit tulisivat mukaan eli ettei se katkaisisi tuota Exceliä. En vai millään saa koodia toimimaan. Minä olen yrittänyt tällä: ActiveWorkbook.SaveAs Filename:=myFolder, FileFormat:=xlText, CreateBackup:=False. Tällä saan kyllä koko Excelin txt-muotoon, mutta sinne tulee ylimääräisiä välejä, joten tiedot eivät mene tietokantaan. Minun pitäisi saada siis noi välit pois. Yritin sitten laittaa koodiin FileFormat:=xlTextPrinter, jolloin välit poistuivat, mutta sitten se myös katkaisi sen Excel-tiedoston ja loppujen sarakkeiden tiedot putosivat pois.
Osaako joku neuvoa miten saisin tämän tehtyä?

Grez [02.06.2014 16:55:56]

Mikset tee tunge sillä VBA-koodilla suoraan tietokantaan? Kuulostaa jotenkin turhalta tuollainen txt-välimuoto.

neosofta [02.06.2014 18:50:47]

Yksi tapa olisi tallentaa excel talu .csv muodossa. Kyseisen tiedostomuodon data on siirrettävissä suoraan tietokantaan. Mikäli soluissa olevat arvot eivät sisällä pilkkuja niin sarake-erottimena voidaan käyttää pilkkua, jolloin homma hoituisi esim. näin

Private Sub CommandButton1_Click()

    'tallennetaan työkirjan tiedostosijainti muuttujaan
    Dim orginal_path As String
    orginal_path = ThisWorkbook.FullName
    'tallennetaan työkirjan tiedostoformaatti muuttujaan
    Dim orginal_format As XlFileFormat
    orginal_format = ThisWorkbook.FileFormat

    'haetaan käyttäjän Omat tiedostot kansion polku
    Dim omat_tiedostot As String
    Set WshShell = CreateObject("WScript.Shell")
    omat_tiedostot = WshShell.SpecialFolders("MyDocuments")
    Set WshShell = Nothing

    'määritellään alikasio
    Dim kansiopolku As String
    kansiopolku = omat_tiedostot & "\csv_data"

    'jos määriteltyä alikansiota ei ole niin se luodaan
    If Dir(kansiopolku, vbDirectory) = "" Then
        MkDir (kansiopolku)
    End If

    'estetään exceliä päivittämästä näyttöä
    Application.ScreenUpdating = False

    'lisätään työkirjaan uusi taulu
    Sheets.Add

    Dim pos As Integer
    pos = InStrRev(ThisWorkbook.Name, ".") - 1
    Dim xlname As String
    xlname = Left(ThisWorkbook.Name, pos)

    Dim kokopolku As String
    'määritellään .csv tiedoston tallennuspolku
    kokopolku = kansiopolku & "\" & xlname & ".csv"

    'kopioidaan taulukon Taul1 käytössä olevan
    'alueen data uuteen nyt aktiiviseen tauluun
    Taul1.UsedRange.Copy ActiveSheet.Range("A1")

    'estetään exceliä näyttämämästä ilmoituksia
    Application.DisplayAlerts = False
    'tallennetaan aktiivinen taulu .csv muodossa
    ActiveSheet.SaveAs kokopolku, xlCSV, Local:=False
    '(eurooppalaisissa järjestelmissä Local:=True
    'asettaa sarake-erottimeksi puolipisteen)

    'tallennetaan acktiivisen taulun nimi muuttujaan
    Dim temppi As String
    'tallennetaan aktiivisen taulun nimi muuttujaan
    temppi = ActiveSheet.Name

    'asetetaan taulukko Taul1 aktiivisesksi
    Taul1.Activate

    'poistetaan väliaikainen työkirjan taulu
    Sheets(temppi).Delete

    'tallennetaan työkirja alkuperäiseen sijaintiin
    'alkuperäisellä nimellä, alkuperäiseen formaattiin
    ThisWorkbook.SaveAs orginal_path, orginal_format

    'sallitaan excelin näyttää ilmoitukset
    Application.DisplayAlerts = True
    'salitaan excelin päivittää näyttö
    Application.ScreenUpdating = True

End Sub

Yllä oleva koodi siis tallentaa väliaikaisen taulun datan .csv tiedostoon jossa sarakkeet on erortettu pilkulla ja rivit rivinvaihtomerkillä.

Mikäli taulun jotkin solut sisältävät dataa jossa on pilkku niin homma oikeastaan helpottuu, kun tehdään oma .csv muotoon tallentava tallennusmekanismi, joka erottelee sarakkeet puolipisteellä.

Private Sub CommandButton1_Click()

    Dim omat_tiedostot As String
    Set WshShell = CreateObject("WScript.Shell")
    omat_tiedostot = WshShell.SpecialFolders("MyDocuments")
    Set WshShell = Nothing

    Dim kansiopolku As String
    kansiopolku = omat_tiedostot & "\csv_data"

    If Dir(kansiopolku, vbDirectory) = "" Then
        MkDir (kansiopolku)
    End If

    Dim pos As Integer
    pos = InStrRev(ThisWorkbook.Name, ".") - 1
    Dim xlname As String
    xlname = Left(ThisWorkbook.Name, pos)

    Dim kokopolku As String
    kokopolku = kansiopolku & "\" & xlname & ".csv"

    Dim vriv As Long, vsarake As Long
    vrivi = Taul1.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row
    vsarake = Taul1.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Column

    'määritellään merkijono muuttuja
    Dim csvdata As String

    For i = 1 To vrivi
        For j = 1 To vsarake
            'lisätää merkkijonoon laskureiden (i,j)
            'määrittämän solun arvo tekstinä
            csvdata = csvdata + Taul1.Cells(i, j).Text
            If j < vsarake Then
                'jos laskurin j arvo on pienempi,
                'kuin muuttujan vsarake arvo niin
                'lisätään merkkijonoon puolipiste
                csvdata = csvdata + ";"
            End If
        Next j
        'jos laskurin i arvo on pienempi, kuin muuttujan vrivi arvo
        If i < vrivi Then
            'niin lisätään merkijonoon rivinvaihtomerkki
            csvdata = csvdata + vbCrLf
        End If
    Next i

     'avataan uusi tiedosto
     Open kokopolku For Output As #1
     'kirjoitetaan merkkijono tiedostoon
     Print #1, csvdata
     'suljetaan tiedosto
     Close #1
     'tyhjennetään merkkijono
     csvdata = ""

End Sub

En puutu tässä sen enempää siihen miten csv-data siirretään tietokantaan, koska se ei liity varsinaisesti VBA-koodiin (vaikkakin importtaus voitaisiin hoitaa sama tien VBA-koodista käsin). Netistä löydät helposti ohjeita .csv datan siirtämiseksi tietokantaan ja miten erotinmerkit missäkin systeemissä määritellään.

Grez [02.06.2014 19:17:19]

Excelissä voi tallettaa suoraan CSV-tiedostoksikin ilman että tarvii VBA-koodia ollenkaan.

neosofta [03.06.2014 00:13:08]

No laitetaan nyt vielä yksinkertainen Excel/VBA-esimerkki csv datan viennistä olemassa olevan Microsoft SQL Server tietokannan tauluun:

Private Sub CommandButton1_Click()

    Dim orginal_path As String
    orginal_path = ThisWorkbook.FullName

    Dim orginal_format As XlFileFormat
    orginal_format = ThisWorkbook.FileFormat

    Application.ScreenUpdating = False
    Sheets.Add

    Dim pos As Integer
    pos = InStrRev(ThisWorkbook.Name, ".") - 1
    Dim xlname As String

    If pos > 0 Then
        xlname = Left(ThisWorkbook.Name, pos)
    Else
        xlname = ThisWorkbook.Name
    End If

    Taul1.UsedRange.Copy ActiveSheet.Range("A1")

    Dim csv_path As String
    csv_path = "c:\" & xlname & ".csv"

    Application.DisplayAlerts = False
    ActiveSheet.SaveAs csv_path, xlCSV, Local:=True

    Dim tmp As String
    tmp = ActiveSheet.Name
    Taul1.Activate
    Sheets(tmp).Delete

    ThisWorkbook.SaveAs orginal_path, orginal_format
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    Dim sqlcmd As String
    sqlcmd = "BULK INSERT dbo.TAULUN_NIMI" + vbCrLf + _
    "FROM '" + csv_path + "'" + vbCrLf + _
    "WITH" + vbCrLf + _
    "(" + vbCrLf + _
    "FIELDTERMINATOR = ';'," + vbCrLf + _
    "ROWTERMINATOR = '\n'" + vbCrLf + _
    ")" + vbCrLf + _
    "GO"

    Open "C:\insert.sql" For Output As #1
    Print #1, sqlcmd: Close #1

    Shell "cmd /C sqlcmd -S SQL_Serverin_Nimi -U käyttäjätunnus -P salasana -d tietokanta -i c:\insert.sql"

End Sub

Mamma [03.06.2014 08:38:11]

Kiitos kovasti vastauksista!
Minä en voi suoraan viedä tiedostoa Vba-koodilla, koska meidän ohjelmaan, joka siis käyttää tuota tietokantaa, on rakennettu palikka,joka vie tekstimuotoisen tiedoston kantaan. Meillä on olemassa tietuekuvaus, jonka mukainen tuon tekstitiedoston täytyy olla, jotta sen saa vietyä kantaan. Näin ollen minun on saatava tiedosto juuri oikean muotoiseksi, eikä siinä saa olla pilkkuja tai muitakaan erottimia. Eli tuo csv-muoto ei kyllä käy.
Tässä on koodi, joka toimii siten, että kaikki Excel-tiedostolla olevat sarakkeet tulevat mukaan, mutta Txt-tiedostoon tulee välejä:

Sub ExcelTeksti()
Dim myFolder As String
 Dim myRange As Range
 ActiveSheet.Activate
Range("A:AJ").Select
 'Ask user for folder to save text file to.
 myFolder = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
 'Save selected data as text file in users selected folder.
 ActiveWorkbook.SaveAs Filename:=myFolder, FileFormat:=xlText, CreateBackup:=False
 'Indicate save action.
 MsgBox "Tekstitiedosto: " & myFolder & " tallennettu!", _
 vbExclamation + vbOKOnly, _
 "File Save Results!"
ActiveWorkbook.Close savechanges:=False
 End Sub

Sitten jos vaihdan tuohon FileFormat:=xlTextPrinter, jää noi välit pois, mutta se katkaisee Excel-tiedoston Z-sarakkeen jälkeen. Eli AA-AJ-sarakkeiden tiedot ovat kyllä tuossa tekstitiedostossa, mutta ne tulevat sinne tiedoston perään, eivätkä mene sieltä kantaan.
Eli tarvitsisin koodin, joka ei tuottaisi välejä siihen tekstitiedostoon, eikä katkaisisi sitä Z-sarakkeen jälkeen?

Mod. lisäsi kooditagit!

Grez [03.06.2014 11:03:57]

Kuulostaa kyllä taas sellaiselta tuotokselta ettei viitsisi hirveästi käsiään työntää siihen.

Mutta mitäs jos muodostat oikean muotoisen tekstitiedoston manuaalisesti VBA-koodilla Neosoftan toisen esimerkin hengessä.

neosofta [03.06.2014 12:15:28]

Jos tarkastellaan tuota netistä löytämääsi aliohjelmaa, joka on aivan perseestä...

Sub ExcelTeksti()

    Dim myFolder As String
    'tässä alustetaan muuttuja range tyyppiin,
    Dim myRange As Range
    'jota ei kuitenkaan missään vaiheessa
    'aseteta eikä edes yritetä käyttää.

    'objektin asettaminen tapahtuu VBA:ssa Set komennolla.
    'Set myRange = ActiveSheet.Range("A:AJ") 'esim.

    'tämä komento on täysin turha
    ActiveSheet.Activate
    'minkä ihmeen takia jo aktiivinen taulu
    'pitäisi aktivoida taas uudestaan???

    '* turha valinta
    Range("A:AJ").Select

    myFolder = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")

    ActiveWorkbook.SaveAs Filename:=myFolder, FileFormat:=xlText, CreateBackup:=False
    '* koska alla oleva englaninnkielinen on paskapuhetta...
    'Save selected data as text file in users selected folder
    '* ...Excel tallentaa kuitenkin aktiivisen taulun kaiken datan

    'tämä ilmoitus on täysin turha koska jos tallennus-
    'metodi aiheuttaa virheen niin VBA ilmoittaa siitä...
    MsgBox "Tekstitiedosto: " & myFolder & " tallennettu!", _
    vbExclamation + vbOKOnly, _
    "File Save Results!"

     ActiveWorkbook.Close savechanges:=False

End Sub

Mamma [03.06.2014 13:16:09]

Kiitos vaan todella kannustavista vastauksista! En ole mikään koodaaja ja löysin tuon koodin netistä ja kokeilin sitä. Ei teidän tarvitse enää tähän käsiänne laittaa, yritän keksiä jotain.....

neosofta [03.06.2014 14:46:57]

Tuon netistä kopsatun koodin voisi muuttaa hieman järkevämpään muotoon:

Sub ExcelTeksti()

    savePath = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")

    'jos GetSaveAsFilename toiminto peruutetaan
    'niin funktio palautaa Boolean arvon False
    If VarType(savePath) = 11 Then
        'jolloin siitä ilmoitetaan käyttäjälle
        MsgBox "Tallentaminen peruttu!"
        'ja poistutaan aliohjelmasta
        Exit Sub
    End If

    On Error Resume Next

    'talentaa datan tekstitiedostoon käyttäen sarake-erottimena Tab-merkkiä
    ActiveWorkbook.SaveAs Filename:=savePath, FileFormat:=xlText, Local:=True

    'jos tallennusrutiini aiheutti virheen
    If Err <> 0 Then
        'niin siitä ilmoitetaan käyttäjälle
        MsgBox Error$
        'Error -objekti nollataan
        Err.Clear
        On Error GoTo 0
        'ja poistutaan aliohjelmasta
        Exit Sub
    End If

    ActiveWorkbook.Close savechanges:=False

End Sub

Mamma kirjoitti:

...Minä en voi suoraan viedä tiedostoa Vba-koodilla, koska meidän ohjelmaan, joka siis käyttää tuota tietokantaa, on rakennettu palikka,joka vie tekstimuotoisen tiedoston kantaan. Meillä on olemassa tietuekuvaus, jonka mukainen tuon tekstitiedoston täytyy olla, jotta sen saa vietyä kantaan...

On ihan varmaa, että kantaan vietävässä tekstitiedostossa on saraketta ilmaiseva erotinmerkki tai muussa tapauksessa palikkanne laskee tekstitiedoston merkkien määriä per rivi, johon en oikein jaksa uskoa. Nyt kun tallennat VBA:lla xlText formaattiin niin Excel iskee sarakkeille erottimeksi Tab merkin.

Grez [03.06.2014 15:00:49]

Ei kun meinasin siis että systeemi jossa tietoa tulee Excelistä, josta ne täytyy muuttaa tekstitiedostoksi että sen saa vietyä tietokantaan kuulostaa juuri sellaiselta tyypilliseltä "ylläpitäjän unelmalta."

Kommenttini ei ollut tarkoitus olla epäkannustava, hienoa jos jaksat sen kanssa tapella. Kyllä sitä itsekin välillä joutuu työntämään käsiään ties mihin jätöksiin vaikkei viitsisikään.

Sivun alkuun

Vastaus

Aihe on jo aika vanha, joten et voi enää vastata siihen.

Kirjautuminen

Haku

Tehtävät

Kilpailu