' CSV-Text.vbs v2.00 ' Copyright (C) 2009 Brent Muir ' ' This script opens a CSV file in Microsoft Excel, ensuring that columns ' are opened as text. This prevents Excel from automatically converting ' data that looks like a date or a number, but should actually remain as ' text (e.g. phone number, or various codes or ID strings). ' ' A simple OpenText command does not work because when Excel sees a ' ".CSV" file extension, it ignores the FieldInfo parameter. So, to get ' around this annoyance, we open the file using the Open method (so that ' Excel can maintain the link to the file), but then we re-read the file ' via a script, inserting the data into the first column. Finally, we ' use the TextToColumns command to parse the comma-separated data. ' ' ' ' This program is free software; you can redistribute it and/or modify ' it under the terms of the GNU General Public License as published by ' the Free Software Foundation; either version 2 of the License, or ' (at your option) any later version. ' ' This program is distributed in the hope that it will be useful, ' but WITHOUT ANY WARRANTY; without even the implied warranty of ' MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the ' GNU General Public License for more details. Dim oXL, oFS, fieldInfo, f, ts, s, i, s_no_quotes, num_quotes Const xlDelimited = 1, xlTextQualifierDoubleQuote = 1 Const ForReading = 1, ForWriting = 2, ForAppending = 8 Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 ' There doesn't seem to be a programmatic way of creating this array ' This fieldInfo array tells Excel to import the first 255 columns as text fieldInfo = Array( _ Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), _ Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), Array(10, 2), _ Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2), Array(15, 2), _ Array(16, 2), Array(17, 2), Array(18, 2), Array(19, 2), Array(20, 2), _ Array(21, 2), Array(22, 2), Array(23, 2), Array(24, 2), Array(25, 2), _ Array(26, 2), Array(27, 2), Array(28, 2), Array(29, 2), Array(30, 2), _ Array(31, 2), Array(32, 2), Array(33, 2), Array(34, 2), Array(35, 2), _ Array(36, 2), Array(37, 2), Array(38, 2), Array(39, 2), Array(40, 2), _ Array(41, 2), Array(42, 2), Array(43, 2), Array(44, 2), Array(45, 2), _ Array(46, 2), Array(47, 2), Array(48, 2), Array(49, 2), Array(50, 2), _ Array(51, 2), Array(52, 2), Array(53, 2), Array(54, 2), Array(55, 2), _ Array(56, 2), Array(57, 2), Array(58, 2), Array(59, 2), Array(60, 2), _ Array(61, 2), Array(62, 2), Array(63, 2), Array(64, 2), Array(65, 2), _ Array(66, 2), Array(67, 2), Array(68, 2), Array(69, 2), Array(70, 2), _ Array(71, 2), Array(72, 2), Array(73, 2), Array(74, 2), Array(75, 2), _ Array(76, 2), Array(77, 2), Array(78, 2), Array(79, 2), Array(80, 2), _ Array(81, 2), Array(82, 2), Array(83, 2), Array(84, 2), Array(85, 2), _ Array(86, 2), Array(87, 2), Array(88, 2), Array(89, 2), Array(90, 2), _ Array(91, 2), Array(92, 2), Array(93, 2), Array(94, 2), Array(95, 2), _ Array(96, 2), Array(97, 2), Array(98, 2), Array(99, 2), Array(100, 2), _ Array(101, 2), Array(102, 2), Array(103, 2), Array(104, 2), Array(105, 2), _ Array(106, 2), Array(107, 2), Array(108, 2), Array(109, 2), Array(110, 2), _ Array(111, 2), Array(112, 2), Array(113, 2), Array(114, 2), Array(115, 2), _ Array(116, 2), Array(117, 2), Array(118, 2), Array(119, 2), Array(120, 2), _ Array(121, 2), Array(122, 2), Array(123, 2), Array(124, 2), Array(125, 2), _ Array(126, 2), Array(127, 2), Array(128, 2), Array(129, 2), Array(130, 2), _ Array(131, 2), Array(132, 2), Array(133, 2), Array(134, 2), Array(135, 2), _ Array(136, 2), Array(137, 2), Array(138, 2), Array(139, 2), Array(140, 2), _ Array(141, 2), Array(142, 2), Array(143, 2), Array(144, 2), Array(145, 2), _ Array(146, 2), Array(147, 2), Array(148, 2), Array(149, 2), Array(150, 2), _ Array(151, 2), Array(152, 2), Array(153, 2), Array(154, 2), Array(155, 2), _ Array(156, 2), Array(157, 2), Array(158, 2), Array(159, 2), Array(160, 2), _ Array(161, 2), Array(162, 2), Array(163, 2), Array(164, 2), Array(165, 2), _ Array(166, 2), Array(167, 2), Array(168, 2), Array(169, 2), Array(170, 2), _ Array(171, 2), Array(172, 2), Array(173, 2), Array(174, 2), Array(175, 2), _ Array(176, 2), Array(177, 2), Array(178, 2), Array(179, 2), Array(180, 2), _ Array(181, 2), Array(182, 2), Array(183, 2), Array(184, 2), Array(185, 2), _ Array(186, 2), Array(187, 2), Array(188, 2), Array(189, 2), Array(190, 2), _ Array(191, 2), Array(192, 2), Array(193, 2), Array(194, 2), Array(195, 2), _ Array(196, 2), Array(197, 2), Array(198, 2), Array(199, 2), Array(200, 2), _ Array(201, 2), Array(202, 2), Array(203, 2), Array(204, 2), Array(205, 2), _ Array(206, 2), Array(207, 2), Array(208, 2), Array(209, 2), Array(210, 2), _ Array(211, 2), Array(212, 2), Array(213, 2), Array(214, 2), Array(215, 2), _ Array(216, 2), Array(217, 2), Array(218, 2), Array(219, 2), Array(220, 2), _ Array(221, 2), Array(222, 2), Array(223, 2), Array(224, 2), Array(225, 2), _ Array(226, 2), Array(227, 2), Array(228, 2), Array(229, 2), Array(230, 2), _ Array(231, 2), Array(232, 2), Array(233, 2), Array(234, 2), Array(235, 2), _ Array(236, 2), Array(237, 2), Array(238, 2), Array(239, 2), Array(240, 2), _ Array(241, 2), Array(242, 2), Array(243, 2), Array(244, 2), Array(245, 2), _ Array(246, 2), Array(247, 2), Array(248, 2), Array(249, 2), Array(250, 2), _ Array(251, 2), Array(252, 2), Array(253, 2), Array(254, 2), Array(255, 2) ) ' Check command line parameters If WScript.Arguments.Count = 0 Then MsgBox "Please provide a .CSV file to this script.", 48, "No file to open" WScript.Quit(1) End If ' Launch Excel and create a new workbook Set oXL = CreateObject("Excel.Application") oXL.Visible = true oXL.Workbooks.Open WScript.Arguments(0) Set oFS = CreateObject("Scripting.FileSystemObject") Set f = oFS.GetFile(WScript.Arguments(0)) Set ts = f.OpenAsTextStream(ForReading, TristateUseDefault) ' Read through lines and insert into cells in Excel. ' If a line has unmatched quotes, then there are cells that include newline ' characters, so keep retrieving lines until quotes are matched. i = 1 Do Until ts.AtEndOfStream s = s & ts.ReadLine s_no_quotes = Replace(s, """", "") num_quotes = Len(s) - Len(s_no_quotes) ' only insert lines into cell if matching quotes have been read If num_quotes Mod 2 = 0 Then oXL.ActiveWorkbook.ActiveSheet.Cells(i, 1).Value = s i = i + 1 s = "" Else ' insert newline when dealing with multi-line cells s = s & Chr(10) End If Loop ' cater for case where last quote is not matched oXL.ActiveWorkbook.ActiveSheet.Cells(i, 1).Value = s ' Temporarily turn off warnings so that we don't get alert about replacing data oXL.DisplayAlerts = False ' Use TextToColumns to split commas into columns oXL.ActiveWorkbook.ActiveSheet.Range("A:A").TextToColumns oXL.ActiveWorkbook.ActiveSheet.Range("A1"), xlDelimited, xlTextQualifierDoubleQuote, false, false, false, true, false, false, "", fieldInfo oXL.DisplayAlerts = True oXL.ActiveWorkbook.Saved = true ts.Close