PDA

View Full Version : copy every 2 rows of data into a pipe delimited text file



rki1966
10-25-2013, 08:00 PM
I have an excel file that has 400 rows of data. I need to copy every 2 rows of data into a pipe delimited text file. I also need to copy row 1 into every file. So, every file with have the first row from the excel file and the next two rows of data. There will be 200 files.

One more note: there is only data through column X, but need the pipes to go through columns AB

Location: c:\user\

Filenames: Loader1.txt, Loader2.txt, etc.

patel
10-25-2013, 10:23 PM
can you attach sample files ?

HaHoBe
10-26-2013, 05:49 AM
Hi, rki1966,

maybe try it like this:

Sub WriteToTextfilfe()

Dim lngCol As Long
Dim lngCounter As Long
Dim lngLoop As Long
Dim intFile As Integer
Dim strFile As String
Dim strText As String

Const cstrPath As String = "c:\user\"

Do While Range("A2").Value <> ""
lngCounter = lngCounter + 1
strFile = cstrPath & "loader" & lngCounter & ".txt"
intFile = FreeFile
Open strFile For Output As intFile
For lngLoop = 1 To 3
For lngCol = 1 To 28
strText = strText & Cells(lngLoop, lngCol).Value & "|"
Next lngCol
strText = Left(strText, Len(strText) - 1)
Print #intFile, strText
strText = ""
Next lngLoop
Close intFile
Range("A2:A3").EntireRow.Delete xlShiftUp
Loop
End Sub
Ciao,
Holger

rki1966
10-26-2013, 09:26 AM
thanks, this is great.

I just need one change which I forgot to indicate. Actually the first row is just text that does not need pipes. Would it be easier to just add the text to the code so the first row will be "~Format=transaction" and the next two rows will be the data set with pipes.

rki1966
10-26-2013, 01:18 PM
I figured it out, thanks

snb
10-26-2013, 02:42 PM
crossposted

http://www.mrexcel.com/forum/excel-questions/735216-copy-every-two-rows-text-file.html#post3615163